SQL SELECT Query Performance between AND and Interlaced SELECT´s

Good evening. I am new to SQL and I am currently learning how to write SQL SELECT statements. I read a lot about the difference of performance between different ways of writing SELECT statement.

I am asking myself about the difference between one SELECT query with a lot AND statements and “Interlaced” SELECT statements.

e.g

SELECT s.name
FROM studenten s, vorlesungen v, hoeren h, professoren p
WHERE p.name='Kant'
    AND s.matr_nr=h.matr_nr
    AND v.vorl_nr=h.vorl_nr
    AND v.gelesen_von=p.pers_nr;

SELECT s.name FROM studenten s, 
    (SELECT h.matr_nr FROM hoeren h , 
        (SELECT v.vorl_nr FROM Vorlesungen v, 
            (SELECT p.pers_nr FROM Professoren p WHERE name ='Kant') AS tab1 
        WHERE tab1.pers_nr = v.gelesen_von) AS tab2 
    WHERE tab2.vorl_nr=h.vorl_nr) AS tab3 
WHERE tab3.matr_nr=s.matr_nr;

Which is better. Or is there maybe a third even better way?

Answer

SELECT s.name
FROM studenten s
JOIN hoeren h USING (matr_nr)
JOIN vorlesungen v USING (vorl_nr)
JOIN professoren p ON (v.gelesen_von = p.pers_nr)
WHERE p.name='Kant';

Note that with USING, you don’t really need the table aliases for hoeren or vorlesungen. You only need aliases to distinguish between studenten.name and professoren.name. Hence, the query could be shortened to

SELECT s.name
FROM studenten s
JOIN hoeren USING (matr_nr)
JOIN vorlesungen USING (vorl_nr)
JOIN professoren p ON (gelesen_von = p.pers_nr)
WHERE p.name='Kant';

Whether you want to keep the table aliases or not is a matter of taste.