Less common Joins

at959131405 · May 5, 2022, 6:11pm

Screen Link:

My Code:

Replace this line with your code

What I expected to happen:

What actually happened:
I am not getting why missing row not reflecting in below query
SELECT *
FROM hue AS h
LEFT JOIN palette AS p
ON h.color = p.colour
WHERE p.number <> 2.;

Will reflect in
SELECT *
FROM hue AS h
LEFT JOIN palette AS p
ON h.color = p.colour AND p.number <> 2;

Since essentially if it is a missing value p.number<>2 will always fails. Why would the missing record reflect with AND condition?

Replace this line with the output/error

the_doctor · May 6, 2022, 9:04pm

Let’s say we run the following -

SELECT t.track_id, il.track_id, t.composer, t.composer NOT LIKE "%Angus%"
  FROM track AS t
  LEFT JOIN invoice_line AS il
    ON t.track_id = il.track_id
    ORDER BY t.track_id;

We will get an output like -

There’s at least one empty value for composer there. Notice how in the last column, values are 0 or empty. If values were non-empty and the condition of NOT LIKE "%Angus%" was met, we would see a 1.

So, that’s our table right now. 0s, 1s or empty/NULL values.

Let’s run the following -

SELECT t.track_id, il.track_id, t.composer, t.composer NOT LIKE "%Angus%"
  FROM track AS t
  LEFT JOIN invoice_line AS il
    ON t.track_id = il.track_id
    WHERE t.composer NOT LIKE "%Angus%"
    ORDER BY t.track_id;

Now we see only 1s in the last column. WHERE clause is filtering out 0s and the empty/NULL values. If we want to include the NULL values, we need to specify that explicitly.

SELECT t.track_id, il.track_id, t.composer, t.composer NOT LIKE "%Angus%"
  FROM track AS t
  LEFT JOIN invoice_line AS il
    ON t.track_id = il.track_id
    WHERE t.composer NOT LIKE "%Angus%" OR t.composer is NULL
    ORDER BY t.track_id;

But, if we move the condition to ON

SELECT t.track_id, t.composer, t.composer NOT LIKE "%Angus%"
  FROM track AS t
  LEFT JOIN invoice_line AS il
    ON t.track_id = il.track_id
    AND t.composer NOT LIKE "%Angus%";

we get

We see all the 0s, 1s and NULL values there.

But also focus on both track_ids there. We are not filtering anything out here. We are joining on those two conditions separately.

It joins the tables on the track_ids being the same, and it also joins the tables when the composer is not Angus. So, we end up getting a table satisfying both those joining conditions at the same time and not like a logical AND.

That’s what they mean by conjunction

the only difference is that the condition we’re checking isn’t an equality but, rather, a conjunction

We are not comparing those two like

1 AND 0 would output 0
1 AND 1 would output 1, and so on

The tables are being joined to satisfy both the conditions, but the WHERE clause is checking for equality as they state. Hopefully this clears it up.

at959131405 · May 7, 2022, 5:13pm

Hello ,
Thank you so much but I am afraid I still didn’t exactly get below points:

In the query
SELECT t.track_id, il.track_id, t.composer, t.composer NOT LIKE “Angus”
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
WHERE t.composer NOT LIKE “Angus”
ORDER BY t.track_id;
we are essentially selecting same column composer why is one column composer name and other a condition 0 or 1. Shouldn’t the second column show all composer name who aren’t Angus?
IS AND with ON behaving differently ? Would it true for OR and Not operator too?
The result of this query
SELECT t.track_id, t.composer, t.composer NOT LIKE “Angus”
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
AND t.composer NOT LIKE “Angus”;
The first record in the result contains Angus even though it should fail because neither is track_id from invoice line table matching track id from track table nor is composer not Angus