SQL Join各种内联外联说明
来源:互联网 发布:期刊投稿数据库要求 编辑:程序博客网 时间:2024/06/04 17:58
Visual Representation of SQL Joins
- Download Visual SQL JOINs examples - 1.09 KB
- Download Visual SQL JOINs cheat sheet - 143 KB
Introduction
This is just a simple article visually explaining SQL JOIN
s.
Background
I'm a pretty visual person. Things seem to make more sense as a picture. I looked all over the Internet for a good graphical representation of SQL JOIN
s, but I couldn't find any to my liking. Some had good diagrams but lacked completeness (they didn't have all the possible JOINs), and some were just plain terrible. So, I decided to create my own and write an article about it.
Using the code
I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:
INNER JOIN
LEFT JOIN
RIGHT JOIN
OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
OUTER JOIN EXCLUDING INNER JOIN
For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN
, RIGHT EXCLUDING JOIN
, andOUTER EXCLUDING JOIN
, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE
clause).
Inner JOIN
This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:
SELECT <select_list> FROM Table_A AINNER JOIN Table_B BON A.Key = B.Key
Left JOIN
This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:
SELECT <select_list>FROM Table_A ALEFT JOIN Table_B BON A.Key = B.Key
Right JOIN
This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:
SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.Key
Outer JOIN
This Join can also be referred to as a FULL OUTER JOIN
or a FULL JOIN
. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:
SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.Key
Left Excluding JOIN
This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:
SELECT <select_list> FROM Table_A ALEFT JOIN Table_B BON A.Key = B.KeyWHERE B.Key IS NULL
Right Excluding JOIN
This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:
SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULL
Outer Excluding JOIN
This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:
SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULL OR B.Key IS NULL
Examples
Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:
TABLE_A PK Value---- ---------- 1 FOX 2 COP 3 TAXI 6 WASHINGTON 7 DELL 5 ARIZONA 4 LINCOLN 10 LUCENTTABLE_B PK Value---- ---------- 1 TROT 2 CAR 3 CAB 6 MONUMENT 7 PC 8 MICROSOFT 9 APPLE 11 SCOTCH
The results of the seven Joins are shown below:
-- INNER JOINSELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PKFROM Table_A AINNER JOIN Table_B BON A.PK = B.PKA_PK A_Value B_Value B_PK---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7(5 row(s) affected)
-- LEFT JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKA_PK A_Value B_Value B_PK---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 4 LINCOLN NULL NULL 5 ARIZONA NULL NULL 6 WASHINGTON MONUMENT 6 7 DELL PC 7 10 LUCENT NULL NULL(8 row(s) affected)
-- RIGHT JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKA_PK A_Value B_Value B_PK---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7NULL NULL MICROSOFT 8NULL NULL APPLE 9NULL NULL SCOTCH 11(8 row(s) affected)
-- OUTER JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKA_PK A_Value B_Value B_PK---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7NULL NULL MICROSOFT 8NULL NULL APPLE 9NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL(11 row(s) affected)
-- LEFT EXCLUDING JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKWHERE B.PK IS NULLA_PK A_Value B_Value B_PK---- ---------- ---------- ---- 4 LINCOLN NULL NULL 5 ARIZONA NULL NULL 10 LUCENT NULL NULL(3 row(s) affected)
-- RIGHT EXCLUDING JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLA_PK A_Value B_Value B_PK---- ---------- ---------- ----NULL NULL MICROSOFT 8NULL NULL APPLE 9NULL NULL SCOTCH 11(3 row(s) affected)
-- OUTER EXCLUDING JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLOR B.PK IS NULLA_PK A_Value B_Value B_PK---- ---------- ---------- ----NULL NULL MICROSOFT 8NULL NULL APPLE 9NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL(6 row(s) affected)
Note on the OUTER JOIN
that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that's how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY
statement).
You can visit the Wikipedia article for more info here (however, the entry is not graphical).
I've also created a cheat sheet that you can print out if needed. If you right click on the image below and select "Save Target As...", you will download the full size image.
History
- Initial release -- 02/03/2009.
- Version 1.0 -- 02/04/2009 -- Fixed cheat sheet and minor typos.
License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Share
About the Author
- SQL Join各种内联外联说明
- SQL Join各种内联外联说明
- sql 内联,外联
- sql 内联和外联
- sql 内联与外联区别
- 数据库的外联和内联知识 Inner Join & Outter Join
- 数据库的外联和内联知识 Inner Join & Outter Join
- 数据库的外联和内联知识 Inner Join & Outter Join
- sql中内联 和外联 区别
- SQL语句中的内联和外联
- SQL内联、外联的简单理解
- SQL 表连接,内联、外联、全连
- SQL内联、外联的简单理解
- SQL内联、外联的简单理解
- SQL内联、外联的简单理解
- SQL表连接,内联、外联、全连
- SQL内联、外联的简单理解
- ms sql 之 内联列别名/外联列别名
- Linux shell的模拟,并实现输出重定向
- Textview设置文字加粗
- 逐次输出位数
- Unity手游iOS内存分析和测试
- STM32与LPC系列ARM资源比较
- SQL Join各种内联外联说明
- 如何用sql语句实现取出前100条记录
- 静态库和动态库
- mysql的“Got error 28 from storage engine”错误
- java 获取jvm运行状态
- HDSF新手须知,全面的HDFS命令
- Django 博客开发教程 15
- 来学JAVA
- 关系型数据库表结构设计技巧