What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

SQL连接类型详解：从内连接到全连接

最新推荐文章于 2024-04-13 17:45:00 发布

weixin_30369041

最新推荐文章于 2024-04-13 17:45:00 发布

阅读量181

点赞数 1

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/hephec/p/4570627.html

本文深入探讨了SQL中的不同连接类型，包括内连接、左连接、右连接、全连接及其语法应用，通过具体示例展示了如何在实际场景中使用这些连接类型。

There are different types of joins available in SQL:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN: returns rows when there is a match in one of the tables.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

WE can take each first four joins in Details :

We have two tables with the following values.

TableA

id  firstName                  lastName
.......................................
1   arun                        prasanth                 
2   ann                         antony                   
3   sruthy                      abc                      
6   new                         abc

TableB

id2 age Place
................
1   24  kerala
2   24  usa
3   25  ekm
5   24  chennai

....................................................................

INNER JOIN

Note :it gives the intersection of the two tables, i.e. rows they have common in TableA and TableB

syntax is :

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;

Result Will Be

firstName       lastName       age  Place
..............................................
arun            prasanth        24  kerala
ann             antony          24  usa
sruthy          abc             25  ekm

LEFT JOIN

Note : will give all selected rows in TableA, plus any common selected rows in TableB.

Syantax

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age   Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL

RIGHT JOIN

Note : will give all selected rows in TableB, plus any common selected rows in TableA.

Syantax

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age     Place
...............................................................................
arun                        prasanth                    24     kerala
ann                         antony                      24     usa
sruthy                      abc                         25     ekm
NULL                        NULL                        24     chennai

FULL JOIN

Note : It is same as union operation, it will return all selected values from both tables.

Syantax

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age    Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL
NULL                        NULL                        24    chennai