Joins understanding is broken. This is definitely not a lap crossing, honestly

It so happened that I conduct quite a lot of interviews for the position of web programmer. One of the obligatory questions I ask is how INNER JOIN differs from LEFT JOIN.

Most often the answer is something like this: "inner join is like an intersection of sets, ie, only what is in both tables remains, and left join is when the left table remains unchanged, and the intersection of sets is added from the right one. For all other lines add null ". Still, it happens, draw intersecting circles.

I was so tired of these answers with intersections of sets and circles, that I even stopped correcting people.

The fact is that this answer is generally incorrect. Well, or at least not accurate.

Let's consider why, and at the same time touch on a couple more subtleties of join-s.

First, the table is not a lot at all. By mathematical definition, in the set all the elements are unique, they are not repeated, and in the tables, in general, this is generally not the case. The second problem is that the term "intersection" only confuses.

( Update . In the comments there are heated debates about set theory and uniqueness. Very interesting, I learned a lot of new things, thanks)

INNER JOIN

Let's take an example right away.

So, we will create two identical tables with one column id, in each of these tables, let there be two rows each with a value of 1 and something else.

INSERT INTO table1
(id)
VALUES
(1),
(1)
(3);

INSERT INTO table2
(id)
VALUES
(1),
(1),
(2);

, , ,

SELECT *
FROM table1
   INNER JOIN table2
      ON table1.id = table2.id;

" ", " ", .

:

| id  | id  |
| --- | --- |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |

??

, CROSS JOIN. - .

CROSS JOIN — . , , 3 , — 2:

select * from t1;

 id 
----
  1
  2
  3

select * from t2;

 id 
----
  4
  5

CROSS JOIN 6 .

select * 
from t1
   cross join t2; 

 id | id 
----+----
  1 |  4
  1 |  5
  2 |  4
  2 |  5
  3 |  4
  3 |  5

, .

t1 INNER JOIN t2 ON condition

— , ,

t1 CROSS JOIN t2  WHERE condition

.. INNER JOIN — condition. -, , , - .

disclaimer: inner join cross join , , , : . .

LEFT JOIN

, , null, , .

, :

insert into t1 
(id)
values
(1),
(1),
(3);

insert into t2
(id)
values
(1),
(1),
(4),
(5);

LEFT JOIN:

SELECT * 
FROM t1
   LEFT JOIN t2 
       ON t1.id = t2.id;

5 , , .

| id  | id  |
| --- | --- |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |
| 1   | 1   |
| 3   |     |

, LEFT JOIN — INNER JOIN (.. , - ), , .

LEFT JOIN :

SELECT * 
FROM t1 
   CROSS JOIN t2
   WHERE t1.id = t2.id

UNION ALL

SELECT t1.id, null
   FROM t1
   WHERE NOT EXISTS (
        SELECT
        FROM t2
        WHERE t2.id = t1.id
   )

, , , ..

ON

, 99% , ON id id . .

, users_stats, ip .

SELECT s.id, c.city 
FROM users_stats AS s
    JOIN cities_ip_ranges AS c
        ON c.ip_range && s.ip

&& — (. ip4r)

ON true, CROSS JOIN

"table1 JOIN table2 ON true"  == "table1 CROSS JOIN table2"

, join- . " ". , join- . .. - - php.

, , .

, , . , . , , , .

O(n!), n — . , , , . CTE; , , , , , .

, . , , 'LEFT JOIN… WHERE… IS NULL', EXISTS. , .

, . , , "".

, , , . — , , . " ". .

Update. — , . .