In any real relational database, all information is distributed in separate tables. Many of the tables have fixed relationships with each other fixed in the diagram. However, using Sql queries, it’s quite possible to build a connection between data that is not inherent in the scheme. This is done by performing the join join operation, which allows you to build relationships between any number of tables and even join seemingly disparate data.
This article will discuss specifically the left outer join. Before starting to describe this type of connection, we will add some tables to the database.
Preparation of the necessary tables
, . : Peoples (), Realty (), Realty_peoples ( , ). , :
Peoples |
id | L_name | F_name | Middle_name | Birthday |
1 | | | | 16.07.2000 |
2 | | | | 29.01.1986 |
3 | | | | 30.04.1964 |
4 | | | | 31.12.1989 |
5 | | | | 14.03.1992 |
6 | | | | 29.01.1985 |
7 | | | | 25.09.1976 |
8 | | | | 01.10.2001 |
:
Realty |
id | address |
1 | . , . , . 7, .6 |
2 | . , . , . 84, . 9, . 5 |
3 | , . , . , . 134, . 85 |
4 | , . , . , . 16, . 137 |
5 | . , . , . 89, . 13 |
- :
Realty_peoples |
id_peoples | id_realty | type |
7 | 3 | |
8 | 3 | |
3 | 5 | |
7 | 1 | |
5 | 4 | |
6 | 4 | |
Left join (Sql) –
:
Table_A LEFT JOIN table_B [{ON } | {USING _}] |
:
« , . , Null - ».
ON, USING , , , .
Left join -
, Peoples . left join sql :
SELECT Peoples.*, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
:
1 |
id | L_name | F_name | Middle_name | Birthday | id_realty | type |
1 | | | | 16.07.2000 | | |
2 | | | | 29.01.1986 | | |
3 | | | | 30.04.1964 | 5 | |
4 | | | | 31.12.1989 | | |
5 | | | | 14.03.1992 | 4 | |
6 | | | | 29.01.1985 | 4 | |
7 | | | | 25.09.1976 | 1 | |
7 | | | | 25.09.1976 | 3 | |
8 | | | | 01.10.2001 | 3 | |
, , .
, Inner join? , , :
1 |
id | L_name | F_name | Middle_name | Birthday | id_realty | type |
3 | | | | 30.04.1964 | 5 | |
5 | | | | 14.03.1992 | 4 | |
6 | | | | 29.01.1985 | 4 | |
7 | | | | 25.09.1976 | 1 | |
7 | | | | 25.09.1976 | 3 | |
8 | | | | 01.10.2001 | 3 | |
, . , , . Left Right, Inner join.
left join sql . :
SELECT Peoples.*, Realty_peoples.id_realty, Realty_peoples.type, Realty.address FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples LEFT JOIN Realty ON Realty.id = Realty_peoples.id_realty |
, :
1 |
id | L_name | F_name | Middle_name | Birthday | id_realty | type | address |
1 | | | | 16.07.2000 | | | |
2 | | | | 29.01.1986 | | | |
3 | | | | 30.04.1964 | 5 | | . , . , . 89, . 13 |
4 | | | | 31.12.1989 | | | |
5 | | | | 14.03.1992 | 4 | | , . , . , . 16, . 137 |
6 | | | | 29.01.1985 | 4 | | , . , . , . 16, . 137 |
7 | | | | 25.09.1976 | 3 | | , . , . , . 134, . 85 |
7 | | | | 25.09.1976 | 1 | | . , . , . 7, .6 |
8 | | | | 01.10.2001 | 3 | | , . , . , . 134, . 85 |
Left join - :
, , :
- , - .
- Where .
. , . , 2, .
, «… From Realty left join Peoples…» , .
, , , , .
, . - , : , ( ).
Left join - : Where
, .
, . left join sql :
FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
, , – «». , left join sql, :
...
, , Null :
1 |
id | L_name | F_name | Middle_name | Birthday | id_realty | type |
5 | | | | 14.03.1992 | 4 | |
6 | | | | 29.01.1985 | 4 | |
7 | | | | 25.09.1976 | 3 | |
8 | | | | 01.10.2001 | 3 | |
, . left join sql .
SELECT Peoples.*, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples LEFT JOIN Realty_peoples ON (Peoples.id = Realty_peoples.id_peoples AND type <> "") |
:
1 |
id | L_name | F_name | Middle_name | Birthday | id_realty | type |
1 | | | | 16.07.2000 | | |
2 | | | | 29.01.1986 | | |
3 | | | | 30.04.1964 | | |
4 | | | | 31.12.1989 | | |
5 | | | | 14.03.1992 | 4 | |
6 | | | | 29.01.1985 | 4 | |
7 | | | | 25.09.1976 | 3 | |
8 | | | | 01.10.2001 | 3 | |
, left join sql , , , / .
, . left join sql , – , , . !