SQL optimization (inner join or selects)

Question

I have a dilemma, I had a teacher that thought me basically that inner joins are hell (he reproved me because I missed the delivery of the final project by 3 mins...), now I have another that tells me that using just selects is inefficient, so I don't know what is white nor black... could someone enlighten me with their knowledge?

Joins

SELECT
    NombreP AS Nombre, 
    Nota 
FROM Lleva 
    INNER JOIN Estudiante ON CedEstudiante = Estudiante.Cedula 
WHERE
    Lleva.SiglaCurso='CI1312';

No Joins

SELECT
    NombreP AS Nombre, 
    Nota 
FROM (
    SELECT 
        Nota, 
        CedEstudiante 
    FROM Lleva 
    WHERE
        SiglaCurso='CI1312'
) AS Lleva, (
SELECT
    NombreP, 
    Cedula 
FROM Estudiante
) AS Estudiante 
WHERE
    CedEstudiante = Estudiante.Cedula;

So which one is more efficient?

This topic could shed some light : https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause especially the answer from rafidheen It's hard to say anything about memory efficiency, we'd have to take a lot of other factors into consideration (size of data, indexes etc.) — Moseleyi, Oct 20 '17 at 03:19
The professor who recommended that you should not use joins is incorrect. A `JOIN` clause is used to combine rows from two or more tables, based on a related column between them. This is an extremely important concept in database development and you should make sure you have a thorough understanding of all types of joins. I couldn't really imagine a complex and efficient query that doesn't make use of joins. — Tom O., Oct 20 '17 at 03:45
i ment to taking specific columns from a table so the memory (ram) has less. i dont know if that's what happen tho... can you do the same on an join? — Banned007, Oct 20 '17 at 04:02
What the heck? Even "No Joins" query is a `CROSS JOIN` with `WHERE`, that, probably, will be considered as `INNER JOIN` from first query. If you have test tables, run this queries with `SET STATISTICS IO ON` before and `SET STATISTICS IO OFF` after both queries. Take a look at statistics (logical reads etc) take a look at execution plans. My guess is that first query is more efficient. — gofr1, Oct 20 '17 at 05:16
@Banned007 gofr is correct. the second version is still joining - and doing it in a way that is difficult to see/understand. It is also using the old-style join based on the where clause - which is **not a best practice**. Use the "inner join" version. If you want to know which is more efficient, you look at the execution plan. I'll guess that the optimizer might generate the same plan for both since it can often overcome poorly written queries if they are not too complex. — SMor, Oct 20 '17 at 14:40
Dude, the joins are more efficient. Just look at how many lines of code it took. SQL databases are DESIGNED for joins — kjmerf, Oct 20 '17 at 18:41
but, if there is a lot of columns, i could use a subquery to reduce the iterations(i don't know if it goes by this..) of the nested loop.. — Banned007, Oct 27 '17 at 21:56

score 0 · Accepted Answer · answered Oct 20 '17 at 18:37

Lets re-write the code so it's easier to understand:

SELECT E.NombreP AS Nombre 
      ,L.Nota 
FROM Lleva L INNER JOIN Estudiante E ON L.CedEstudiante = E.Cedula 
WHERE
    L.SiglaCurso='CI1312';

A table using a subquery may look like this:

SELECT L.Nota  
      ,(SELECT E.Nombre 
          FROM Estudiante E 
         WHERE E.Cedula = L.CedEstudiante
       )
FROM Lleva L 
WHERE
    L.SiglaCurso='CI1312'

What you actually did in your original query was an implicit join. This is similar to inner join, without declaring the exact joining conditions. Implicit joins will attempt to join on similarly named columns between tables. Most programmers do not advise or use implicit join.

As for join versus subquery, they are applied in different situations.

They are not equivalent. Notice what I have put in bold below:

A subquery will guarantee 1 returned value or return NULL; if there are multiple values returned in the subquery you will get an error for returning more than 1 value and have to solve the problem with an aggregation perhaps (max value, top 1 value). Subqueries that return no match will return NULL without affecting the rest of the row.

The JOIN (INNER JOIN) operates differently. It can match rows and get the single value you're looking for just like a subquery. But a join can multiply returned rows if the joining conditions are not distinct (singular/non-repeating). This is why joins are usually done on Primary Keys (PK's). PK's are distinct by definition. The INNER JOIN will also remove rows if a joining condition doesn't occur between tables. This may be what your first professor was trying to explain- an INNER JOIN can work in many cases - similar to a subquery- but can also can return additional rows or remove rows from the output.

You are correct, however, this is a bit different than a sub-query aliased join. — Ross Bush, Oct 20 '17 at 18:49
You're talking about the 'No join' code that was actually an implicit join? — Zorkolot, Oct 20 '17 at 19:11
Yes, you said "subquery will guarantee 1 returned value or return NULL". That is true for a subquery on the select statement, however, this is not true of a subquery used in a join. — Ross Bush, Oct 20 '17 at 19:16
A 'subquery used in a join' is a 'corelated subquery'. It's given that definition for a reason... I would not confuse that with a subquery in a select statement. — Zorkolot, Oct 20 '17 at 20:41

SQL optimization (inner join or selects)

1 Answers1