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