Left join, right join, inner join

Preface: Do not sum up and forget it!

Let's give a general explanation first.

Example Table

Aid	Adate
1	A1
2	A2
3	A3

Table B

Bid	Bdate
1	B1
2	B2
4	B4

Problem: two tables A and B are connected and fields with the same ID must be retrieved.

Select * from a inner join B on A. Aid = B. Bid this is to retrieve only matching data.

In this case, the following information is taken:

1 A1 B1

2 A2 B2

Then left join refers:

Select * from a left join B on A. Aid = B. Bid

First, retrieve all the data in Table A, and then add the data that matches table A and table B.

In this case, the following information is taken:

1 A1 B1

2 A2 B2

3 A3 null characters

Right join is also available.

This means that all data in Table B is retrieved first, and then the data matching A and B is added.

In this case, the following information is taken:

1 A1 B1

2 A2 B2

4 blank characters B4

====================================================================

The following are official answers

SQL left join keyword

The left join keyword returns all rows from the left table (table_name1), even if no matching row exists in the right table (table_name2.

Left join keyword syntax

Select column_name (s)

From table_name1

Left join table_name2

On table_name1.column_name = table_name2.column_name

Note:In some databases, left join is called left Outer Join.

Original table (used in the example ):

"Persons" table:

Id_p	Lastname	Firstname	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" table:

Id_o	Orderno	Id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

Left join instance

Now we want to list all people and their orders-if any.

You can use the following SELECT statement:

Select persons. lastname, persons. firstname, orders. orderno

From persons

Left join orders

On persons. id_p = orders. id_p

Order by persons. lastname

Result set:

Lastname	Firstname	Orderno
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

The left join keyword returns all rows from the left table (persons) even if no matching row exists in the right table (orders.

 

================================== Below is a popular saying ======================================

A left join B indicates that the number of connected records is the same as the number of records in Table A, that is, Table A is the master table.

A right join B indicates that the number of connected records is the same as the number of records in Table B, that is, table B is the master table.

A left join B is equivalent to B right join

 

 

table    A:  

   Field_K,    Field_A  

 

   1                        a  

 

   3                        b  

 

   4                        c  

 

   table    B:  

   Field_K,    Field_B  

 

   1                        x  

 

   2                        y  

 

   4                        z  

Left join:

   select    a.Field_K,    a.Field_A,    b.Field_K,    b.Field_B  

   from    a    left    join    b    on    a.Field_K=b.Field_K  

Result:

   Field_K          Field_A        Field_K          Field_B          

   ----------            ----------        ----------        ----------    

 

   1                      a                      1                   x                         

 

   3                      b                      NULL             NULL  

 

   4                      c                      4                   z                    

 

Right join:

   select    a.Field_K,    a.Field_A,    b.Field_K,    b.Field_B  

   from    a    right    join    b    on    a.Field_K=b.Field_K  

Result:

   Field_K          Field_A          Field_K          Field_B          

   ----------        ----------           ----------            ----------    

 

   1                      a                      1               x                    

 

   NULL             NULL              2                y

 

   4                      c                      4                 z

 

For example:

Assume that the data in Table A and table B is like this.

   a                          b    

   id      name         id      stock   

 

   1        a              1          15  

 

   2        b              2          50  

   3        c                   

 

   select    *    from    a    inner    join    b    on    a.id=b.id  

 

This syntax is an internal join in the connection query. The result is that records matching the two tables are displayed in the result list.

According to the preceding table, the result is as follows:

   a.id      name      b.id      stock  

   1        a              1          15  

   2        b              2          50  

   ----------------------------  

   select    *    from    a,b    where    a.id=b.id  

This syntax is another method of writing internal join, and the execution result is the same as that of inner join.

   --------------------------------    

   select    *    from    a    left/right    join    b    on    a.id=b.id  

This is the left Outer Join or right Outer Join in the outer join syntax.

If it is a left outer join, it will display all records in Table,

   select    a.*,b.*    from    a    left    join    b    on    a.id=b.id  

The query result is as follows:

   a.id      name      b.id      stock  

   1          a          1               15  

   2          b          2               50  

   3          c        null            null   

   --------------------------------------------  

 

If it is a right outer join, it will display all the records in Table B,

   select    a.*,b.*    from    a    right    join    b    on    a.id=b.id  

The query result is as follows:

   a.id      name      b.id      stock  

   1          a          1              15  

   2          b          2              50   

select    a.*,b.*    from    a    left    join    b    on    a.k    =    b.k    

   select    a.*,b.*    from    a    left    outer    join    b    on    a.k    =b.k  

---------- The above two types are the same. Left join is short for left Outer Join.

   select    a.*,b.*    from    a    left    inner    join    b    on    a.k    =    b.k    

No such statement. It is a wrong statement.

Organized a chart

Go to: blog

 

Left join, right join, inner join