La compréhension des jointures est rompue. Ce n'est certainement pas l'intersection de cercles, honnêtement

Il se trouve que j'ai mené plusieurs interviews pour le poste de programmeur web. L'une des questions que je pose est de savoir en quoi le INNER JOIN diffère du LEFT JOIN.

La réponse la plus courante est quelque chose comme ceci: "la jointure interne est comme une intersection d'ensembles, c'est-à-dire qu'il ne reste que ce qui reste dans les deux tables, et la jointure gauche est lorsque la table de gauche reste inchangée et l'intersection d'ensembles est ajoutée à partir de la droite. Pour toutes les autres lignes sont nulles ". Il arrive aussi qu'ils dessinent des cercles qui se croisent.

J'étais tellement fatigué de ces réponses avec des intersections d'ensembles et de cercles que j'ai même arrêté de corriger les gens.

Le fait est que cette réponse est généralement incorrecte. Eh bien, ou du moins pas précis.

Voyons pourquoi, et en même temps abordons quelques subtilités supplémentaires de jointures.

Tout d'abord, une table n'est pas beaucoup du tout. Par définition mathématique, dans l'ensemble, tous les éléments sont uniques, ils ne sont pas répétés, et dans les tableaux dans le cas général, ce n'est pas le cas. Le deuxième problème est que le terme «intersection» ne fait que confondre.

( Mise à jour . Dans les commentaires, il y a un débat animé sur la théorie des ensembles et l'unicité. Très intéressant, j'ai appris beaucoup de nouvelles choses, merci)

INNER JOIN

Donnons tout de suite un exemple.

Donc, nous allons créer deux tables identiques avec une colonne id, dans chacune de ces tables, il y aura deux lignes avec une valeur de 1 et autre chose.

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. : https://habr.com/ru/post/450528/