Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Last answered:

19 Jun 2023

Posted on:

10 Aug 2022

2

Resolved: Can't understand what LEFT JOIN does

Good morning. The resources say that "left joins can deliver a list with all records from the left table that
do not match any rows from the right table". So, does t1 LEFT JOIN t2 show the records of t1 that are not in t2? And if so, if t1 has 11 rows and t2 has 26 rows, shouldn't t1 LEFT JOIN t2 have at maximum 11 rows?
Instead, if t1 LEFT JOIN t2 shows the records in t1 that are not in t2 + the records they share, isn't this like showing all table t1? Thank you

10 answers ( 3 marked as helpful)
Instructor
Posted on:

12 Aug 2022

0

Hi Alessandro!
Thanks for reaching out.

Yes, your logic is correct. When using LEFT JOIN the order of the tables matters (when using INNER JOIN=JOIN the order does not matter!). So, every record, that is contained in the left table, (the first one) and is not contained in the right table, (the second one) will be included in the result set.

Hope this helps.
Best,
Tsvetelin

Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Posted on:

13 Aug 2022

0

So if t1 has 11 rows and t2 has 26 rows, shouldn't t1 LEFT JOIN t2 have at maximum 11 rows?

Instructor
Posted on:

15 Aug 2022

0

Hi Alessandro!
Thanks for reaching out.

The result set will contain 11 rows only if these rows are not contained in the t2 table. If some of these rows are contained in the t2 table, then the result set will contain 11-n rows where n is the number of rows contained in both tables.

Hope this helps.
Best,
Tsvetelin

Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Posted on:

01 Sept 2022

0

Good morning. It was exactly how I thought it worked, so why the following code returns 26 rows and not at maximum 11?

SELECT
    d.dept_no, m.emp_no, d.dept_name
FROM
    departments_dup d
        LEFT JOIN
    dept_manager_dup m ON m.dept_no = d.dept_no
ORDER BY m.dept_no;

departments_dup contains only 11 rows; dept_manager_dup contains 26 rows. If it works as you said, I expect at maximum 11 rows, but it gives me 24 rows. Why? Thanks

Posted on:

02 Sept 2022

2

Hello Allesandro,

I think the reason for the result having more than 11 rows is that the columns ON m.dept_no = d.dept_no is not a one-to-one match, it is one-to-many.

Take the dept_no 'd009' for example: there are four records in the dept_manager_dup table with 'd009' as their dept_no, but all of them satisfy the match ON m.dept_no = d.dept_no. Considering all these match cases, we already have 20 rows in total. In terms of the Venn diagram, we only have the intersection of the two tables (with respect to dept_no).

image.png

Now, since we are using LEFT JOIN, we must also add all dept_no in departments_dup table to the result, which are 'd001', 'd010', 'd011', and Null (Public Relations). In the Venn diagram, these four records fill the region below. Then we add all the records from that region, hence the 24 records.

image.png



Maybe the logic explained above only holds true when the reference columns for ON are uniquely matched.

Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Posted on:

05 Sept 2022

0

Ok thank you. So I think that if t1 has r1 rows and t2 has r2 rows, the maximum number of rows of t1 left join t2 is r1 * r2, isn't it? But I can't understand one thing: the resources  say that "left joins can deliver a list with all records from the left table that do not match any rows from the right table". But you said that we must add either this (second image) or the common rows of the two tables (first image). So what of the two things is the result of t1 left join t2? First image + second image or just second image? Thanks

Posted on:

08 Sept 2022

0

Hello Alessandro,

The result of t1 left join t2 would the the sum of the records found in each image. In terms of the resulting table in the lecture, the records below fall within the second image. These are records from t1 that do not match any rows from t2.

image.png

And here are the records where there is a (one-to-many) match in m.dept_no = d.dept_no.

image.png



When it comes to the maximum number of rows, I am not certain if it is possible to reach r1 * r2 records with left join, as that number of records are attainable with Cross join only (as far as I know).

Hope this helps.

Super learner
This user is a Super Learner. To become a Super Learner, you need to reach Level 8.
Posted on:

09 Sept 2022

1

Ok so the resources are just confusing with the statement "left joins can deliver a list with all records from the left table that do not match any rows from the right table", it's simply incomplete. Thank you very much!

Posted on:

17 Dec 2022

0

Agreed with Alessandro Imbrìaco. The left join explanation in this recording is simply incorrect.

Posted on:

19 Jun 2023

0

The key part of the statement "left joins can deliver a list with all records from the left table that do not match any rows from the right table" is the 'can'.

While left joins can do the above, it does not mean that that's only what they do when they are used.

In the "Left Join - Part II" lecture, the left join by default returns 1) the instersection of the two tables  +  2)the rows that belong to the left table only.

To extract out only 2) the WHERE clause comes into play which happens to be satisfied when the rightmost column in SELECT (d.dept_name) is NULL -> It is this additional filtering with WHERE that achieves the extraction of 2) from the left join in this particular example.

Submit an answer