SQL left join instance and syntax usage

SQL left join instance and syntax usage

The left JOIN and similar right JOIN operations can be used in any FROM clause to combine records in two tables. The left JOIN operation is used to create a left Outer JOIN, including all records from the first (left) of two tables, even if they do not match the second record value. The right JOIN operation is used to create a right Outer JOIN, including two tables from the second (all records on the right), even if there is no matching value for the first record.
 
Although the left join or right join operation can be nested in Inner Mongolia, the opposite is not true. Internal JOIN operations cannot be nested in a left JOIN or right join operation.
 
When tables 'employees' and 'project' are used, the left JOIN operation returns all employees, whether or not they are currently engaged in any project, but the project name, if applicable, is:

SELECT Employee. username, Project. ProjectName
FROM Employee left join Project
ON Employee. EmployeeID = Project. EmployeeID;
 

If the same table is used, the correct JOIN operation will return the names of all projects, whether it does not belong to any employee for their current work, but where applicable with the employee name

SELECT Employee. username, Project. ProjectName
FROM Employee right join Project
ON Employee. EmployeeID = Project. EmployeeID;
 

Example Table
Aid adate
1 a1
2 a2
3 a3
Table B
Bid bdate
1 b1
2 b2
4 b4
Two tables a and B are connected. fields with the same id must be retrieved.
Select * from a inner join B on a. aid = B. bid this is only used to retrieve 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

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 database tutorials, 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 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 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.
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