SQL JOIN, you should know all about it

introduce

This is an article on SQL JOINs, this article is a domestic bosses translation of an article, in order to make it easier to understand, LZ in MySQL, for example in the back with the sample, the original link is: https://www.cnblogs.com/xufeiyang/p/5818571.html

background

I don’t like abstractions. A picture is worth a thousand words. I searched all the explanations about SQL JOIN on the Internet, but could not find one that could be graphically described.

Some have pictures but they do not cover all JOIN examples, and some descriptions are simply too blank to read. So I decided to write my own article on SQL JOINs.

Detailed instructions

Next I’ll discuss seven ways you can get data from two associated tables, excluding cross JOIN and self JOIN cases. Examples of seven JOINs are:

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. OUTER JOIN

5. LEFT JOIN EXCLUDING INNER JOIN

6. RIGHT JOIN EXCLUDING INNER JOIN

7. OUTER JOIN EXCLUDING INNER JOIN

In order to better describe this article, I specify 5,6,7 as LEFT EXCLUDING INNER JOIN, RIGHT EXCLUDING INNER JOIN, OUTER EXCLUDING INNER JOIN

Some might disagree: 5, 6, and 7 are not really joins of two tables; But for ease of understanding, I’ll stick with JOINs, because you’ll probably use JOINs in every query (excluding some records with WHERE conditions)

INNER JOIN

This is the simplest, easiest to understand, and most commonly used form of JOIN. The inner join query returns the result of all matching rows in tables A and B. SQL example:

SELECT <select_list> FROM Table_A AINNER JOIN Table_B BON A.Key = B.KeyCopy the code

LEFT JOIN

The LFET JOIN query returns all the records in table A, regardless of whether there are any matches in table B. It returns all matches in table B (those that do not match are marked as null). SQL example:

SELECT <select_list>FROM Table_A ALEFT JOIN Table_B BON A.Key = B.KeyCopy the code

RIGHT JOIN

The opposite of LEFT JOIN. The RIGHT JOIN query returns all the records in table B, regardless of whether there are any matches in table A. It will return all matches in table A (any that do not match will be marked null). SQL example:

SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.KeyCopy the code

OUTER JOIN

OUTER JOIN can also be called FULL OUTER JOIN or FULL JOIN. It returns all rows from both tables, with left table A matching right table B and right table B matching left table A (null if there is no match). OUTER JOIN is written like this:

SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.KeyCopy the code

LEFT Excluding JOIN

It returns all rows in table A that are not in table B, usually written as:

SELECT <select_list> FROM Table_A ALEFT JOIN Table_B BON A.Key = B.KeyWHERE B.Key IS NULLCopy the code

RIGHT Excluding JOIN

Instead, it will return all rows from table B that are not in table A.

SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULLCopy the code

OUTER Excluding JOIN

Outer Excluding JOIN returns all rows from tables A and B that do not match. I have not encountered this situation, but other joins are frequently used. SQL example:

SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULL OR B.Key IS NULLCopy the code

example

Take MySQL as an example. The data prepared is as follows. To make it easier to understand, the data is regular

The teacher table

Teacher_card table

1, 2, and 3 are unique to teacher table, 4, 5, and 6 are public, and 7, 8, and 9 are unique to Teacher_card table

INNER JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tINNER JOIN teacher_card tcON t.tid = tc.tidCopy the code

The INNER keyword can be omitted

lEFT JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tLEFT OUTER JOIN teacher_card tcON t.tid = tc.tidCopy the code

The OUTER keyword can be omitted

RIGHT JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tRIGHT OUTER JOIN teacher_card tcON t.tid = tc.tidCopy the code

The OUTER keyword can be omitted

OUTER JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tFULL OUTER JOIN teacher_card tcON t.tid = tc.tidCopy the code

MySQL does not support FULL OUTER JOIN (OUTER JOIN)

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tLEFT JOIN teacher_card tcON t.tid = tc.tidUNIONSELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tRIGHT JOIN teacher_card tcON t.tid = tc.tidCopy the code

LEFT EXCLUDING JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tLEFT JOIN teacher_card tcON t.tid = tc.tidWHERE tc.tid IS NULLCopy the code

RIGHT EXCLUDING JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tRIGHT JOIN teacher_card tcON t.tid = tc.tidWHERE t.tid IS NULLCopy the code

OUTER EXCLUDING JOIN

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tFULL OUTER JOIN teacher_card tcON t.tid = tc.tidWHERE t.tid IS NULLOR tc.tid IS NULLCopy the code

For the same reason, MySQL cannot be written in the form above, but can be rewritten as follows

SELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tLEFT JOIN teacher_card tcON t.tid = tc.tidWHERE tc.tid IS NULLUNIONSELECT t.tid, t.name, tc.tid AS tcid, tc.descriptionFROM teacher tRIGHT JOIN teacher_card tcON t.tid = tc.tidWHERE t.tid IS NULLCopy the code

I’ll put a big picture at the end

Recommended reading

Why is MySQL index implemented in B+ tree?

The principle and application of ClassLoader

The loading order of Java classes

Java knowledge hall

A public account with dry goods

---