未加星标

Understanding JOINs in SQL Server

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

During my work I get the chance reviewing lots of T-SQL Procedures and Views and I often see that the SQL joins are mis-used in them. When I enquire the developers regarding this, it’s evident that most of the time it has been the case that they don’t have the proper understanding what each JOIN exactly does or how it behaves, ultimately causing the SQL Procedure or the View to return an unexpected resultset. Therefore I thought of writing this blog post.

When we require to fetch details from multiple tables the JOIN caluse is there for the rescue. But in SQL Server there are various types of JOINs which will cater our requirement in different ways. So it’s very important to have a good understanding in these types of JOINs and their usage.

In SQL Server following types of JOINs available.

INNER JOIN OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN CROSS APPLY OUTER APPLY

We will look into the afrementioned JOINs more closely. The scope of this article is to give a high-level idea on the aforementioned JOINs and the APPLY operator in SQL Server.

To illustrate the aforementioned JOINs I will use the following sample tables:

SalesRep SalesDetails RepRating Settings

We consider a case where we have 5 Sales Reps and the details will be saved in ‘RepDetails’ table and the sales transactions which they have done is recorded under ‘SalesDetails’ table. In the SalesDetails table we have included few transactions which we don’t have a matching Sales Rep. Similarly in the RepDetails table there are couple of sales reps which we don’t have any sales infromation.

--== Create Tables ==-- CREATE TABLE RepDetails( RepId INT ,RepName VARCHAR(30) ) CREATE TABLE SalesDetails( RepId INT ,SaleMonth VARCHAR(6) ,OrderNo VARCHAR(6) ,SaleValue MONEY ) CREATE TABLE RepRating( RepId INT ,Rate INT ,YearMonth VARCHAR(6) ) CREATE TABLE Settings( S_Id INT ,S_Desc VARCHAR(20) ,S_Value VARCHAR(20) ) --== Populate Sample Data ==-- INSERT INTO RepDetails ( [RepId] ,[RepName] ) VALUES (1,'Eugene Thomas') ,(2,'John Wheeler') ,(3,'Curtis Bailey') ,(4,'Jeffrey Garrett') ,(5,'Rosemarie Hubbard') INSERT INTO SalesDetails ( [RepId] ,[SaleMonth] ,[OrderNo] ,[SaleValue] ) VALUES (7,'201607','XpyDy3',839) ,(1,'201607','NR0RTp',496) ,(4,'201607','4552T4',299) ,(6,'201607','GKhkyC',877) ,(4,'201606','iyK65Z',291) ,(6,'201606','NFCszW',446) ,(7,'201606','D238bN',135) ,(1,'201607','bERDXk',304) ,(7,'201608','nykZqB',935) ,(4,'201608','R7ea5v',352) ,(6,'201606','VVjIdo',407) ,(7,'201608','vtLT4z',977) ,(2,'201608','xnHTnO',416) ,(1,'201606','jFAJIm',674) ,(6,'201606','0Q011m',480) INSERT INTO dbo.RepRating( RepId ,Rate ,YearMonth ) VALUES (1,1,'201608') ,(3,2,'201608') ,(4,1,'201609') ,(2,2,'201609') INSERT INTO dbo.Settings( S_Id ,S_Desc ,S_Value ) VALUES (1,'LedgerMonth','201609') ,(2,'TaxRate','10%')

**Note: During the illustraion I will refer the table which is followed by the ‘FROM’ clause as the ‘Left Table’ and the table which is follwed by the JOIN clause as the ‘Right Table’.

INNER JOIN / JOIN

When we join two or more tables using an INNER JOIN, it will only return us the results when records can only be found on both left and right tables which will satisfy the condition we supply.


Understanding JOINs in SQL Server

This can be illustrated using a venn diagram as follows:


Understanding JOINs in SQL Server
SELECT * FROM dbo.RepDetails AS RD JOIN dbo.SalesDetails AS SD ON SD.RepId = RD.RepId
Understanding JOINs in SQL Server

**Please note: We have sales reps having RepId’s 1,2,3,4, & 5. But in SalesDetails table we have sales details for RepId’s 1,2,4,6 &7. So when these tables are joined the RepId’s which resides on both tables, which are 1,2, and 4 will return the details, ultimately giving us the aforementioned result set.

LEFT OUTER JOIN / LEFT JOIN

In a LEFT OUTER JOIN, unlike the INNER JOIN, it will select all the records from the ‘Left’ table and based on the JOIN condition, it will select any matching records from the ‘Right’ table and return us the results. If there are no matching details on the ‘Right’ table, columns on related to those rows will return as ‘NULL’.


Understanding JOINs in SQL Server

This can be shown using a venn diagram as follows:


Understanding JOINs in SQL Server
SELECT * FROM dbo.RepDetails AS RD LEFT JOIN dbo.SalesDetails AS SD ON SD.RepId = RD.RepId
Understanding JOINs in SQL Server
RIGHT OUTER JOIN / RIGHT JOIN

In a RIGHT OUTER JOIN, it will select all records from the ‘Right’ table and based on the JOIN condition it will select any matching records from the left table and return. If there aren’t any matching records on the left table it will return a ‘NULL’ value.


Understanding JOINs in SQL Server

This can be shown using a venn diagram as follows:


Understanding JOINs in SQL Server
SELECT * FROM dbo.SalesDetails AS SD RIGHT JOIN dbo.RepDetails AS RD ON SD.RepId = RD.RepId
Understanding JOINs in SQL Server
FULL OUTER JOIN / FULL JOIN

FULL OUTER JOIN is kind of a mx of both LEFT & RIGHT OUTER JOINs. It will return all rows from both ‘Left’ and ‘Right’ tables based on the JOIN condition. When the details aren’t matched it will return a NULL value in those respective columns.


Understanding JOINs in SQL Server

This can be shown using a venn diagram as follows:


Understanding JOINs in SQL Server
SELECT * FROM dbo.RepDetails AS RD FULL OUTER JOIN dbo.SalesDetails AS SD ON SD.RepId = RD.RepId
Understanding JOINs in SQL Server
CROSS JOIN

CROSS JOIN will return a result set which the number of rows equal to rows in ‘Left’ table multiplied by the number of rows in ‘Right’ table. Usually this behaviour is present when there’s no condition provided in the WHERE condition. So each row in the left table is joined to each row in the right table. Usuually this behaviour is called ‘Cartisian Product’


Understanding JOINs in SQL Server
SELECT * FROM dbo.RepDetails AS RD CROSS JOIN dbo.Settings AS S
Understanding JOINs in SQL Server

But when some condition is provided via the WHERE clause CROSS JOIN will behave like an INNER JOIN

SELECT * FROM dbo.RepDetails AS RD CROSS JOIN dbo.Settings AS S WHERE RD.RepId = S.S_Id
Understanding JOINs in SQL Server

**Note: In a CROSS JOIN it’s not possible to refer to a value in the Left table along with the right table. Example following code will result in an error.

SELECT * FROM dbo.RepDetails AS RD CROSS JOIN (SELECT * FROM dbo.Settings AS S WHERE S.S_Id = RD.RepId ) AS ST CROSS APPLY behaves like an INNER

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLUTSQL ServerFUNFC
分页:12
转载请注明
本文标题:Understanding JOINs in SQL Server
本站链接:http://www.codesec.net/view/481121.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(29)