SQL LEFT join instance and syntax usage

Source: Internet
Author: User

SQL LEFT join instance and syntax usage

Left joins and similar rights join operations can be used in any FROM clause to combine records from two tables. The left JOIN operation is used to create a left outer join, including all records from the first (left) of the two tables, even if there is no match in the second record value. The right JOIN operation creates a right outer join, including the two tables from the second (right), even if there is no matching value for the first record.

Although a LEFT JOIN or right JOIN operation can be nested inside Inner Mongolia, otherwise it is not. The inner JOIN operation cannot have a left nested join or right join.

Using table ' employees ' and ' works ', the left JOIN operation will return all employees, regardless of whether they are currently engaged in any project, but the project name if applicable name:


SELECT Employee.username, Project.projectname
From Employee left JOIN Project
on employee.employeeid = Project.employeeid;

Using the same table, the correct JOIN operation will return all item names, whether they are not part of any employee's current work, but where applicable with the employee's name

SELECT Employee.username, Project.projectname
From Employee right JOIN Project
on employee.employeeid = Project.employeeid;


Example Table A
Aid Adate
1 A1
2 A2
3 A3
Table B
Bid Bdate
1 B1
2 B2
4 B4
Two table a,b connected, to remove fields with the same ID
SELECT * from a INNER join B on a.aid = B.bid This is the only matching data to be fetched.
At this point, the removal is:
1 A1 B1
2 A2 B2
So the left join means:
SELECT * from a LEFT join B on a.aid = B.bid
First remove all the data from table A and then add the data that matches the A,b
At this point, the removal is:
1 A1 B1


SQL left JOIN keyword
The left JOIN keyword returns all rows from the left-hand table (TABLE_NAME1), even if there are no matching rows 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 database tutorials, the left join is called the left OUTER join.

The original table (in the example):
"Persons" table: id_p LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth 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 connection (left JOIN) instance
Now, we want to list all the people, as well as their order-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-hand table (Persons), even if there are no matching rows in the right table (Orders).
2 A2 B2
3 A3 NULL characters
There is also the right join
It means to first remove all the data from table B and then add the data that matches the a,b.
At this point, the removal is:
1 A1 B1
2 A2 B2
4 NULL character B4

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.