Left join (SQL) - example, detailed description, usage errors

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) –

left join sql example

:

Table_A LEFT JOIN table_B [{ON } | {USING _}]

:

left join sql examples








« , . , 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 - :

, , :









  1. , - .
  2. Where .

. , . , 2, .

, «… From Realty left join Peoples…» , .

left join sql query example




, , , , .

, . - , : , ( ).

Left join - : Where

, .

, . left join sql :

FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples;

, , – «». , left join sql, :

...

Where type <> ""

, , 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 simple example explanation




, . left join sql , – , , . !




All Articles