SQL Server Brain Basher of the Week #030 - TSQL

Here is the question of the week, it is based on SELECT statements and join mechanism can be used.

Assume that you have two tables like below with data inserted;

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. CREATE TABLE dbo.Customer  
5. (  
6.  CustomerId int identity(1,1) PRIMARY KEY  
7.  , LastName varchar(50) NOT NULL  
8.  , Country varchar(50) NULL  
9. )  
10. GO  
11.   
12. CREATE TABLE dbo.Employee  
13. (  
14.  CustomerId int identity(1,1) PRIMARY KEY  
15.  , LastName varchar(50) NOT NULL  
16.  , Country varchar(50) NULL  
17. )  
18. GO  
19.   
20. INSERT INTO dbo.Customer  
21.  (LastName, Country)  
22. VALUES  
23.  ('Knight', 'United Kingdom')  
24.  , ('Jack', 'United States')  
25.  , ('Brown', NULL)  
26.  , ('Hamilton', 'Italy');  
27.   
28. INSERT INTO dbo.Employee  
29.  (LastName, Country)  
30. VALUES  
31.  ('Khanna', 'India')  
32.  , ('Brown', NULL)  
33.  , ('Jack', 'United States')  
34.  , ('Philips', 'Italy');  

USE tempdb;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) PRIMARY KEY
 , LastName varchar(50) NOT NULL
 , Country varchar(50) NULL
)
GO

CREATE TABLE dbo.Employee
(
 CustomerId int identity(1,1) PRIMARY KEY
 , LastName varchar(50) NOT NULL
 , Country varchar(50) NULL
)
GO

INSERT INTO dbo.Customer
 (LastName, Country)
VALUES
 ('Knight', 'United Kingdom')
 , ('Jack', 'United States')
 , ('Brown', NULL)
 , ('Hamilton', 'Italy');

INSERT INTO dbo.Employee
 (LastName, Country)
VALUES
 ('Khanna', 'India')
 , ('Brown', NULL)
 , ('Jack', 'United States')
 , ('Philips', 'Italy');

And for getting records that are exist in both tables, following queries are written;

view plainprint?

1. SELECT c.LastName, c.Country  
2. FROM dbo.Customer c  
3.  INNER JOIN dbo.Employee e  
4.   ON c.LastName = e.LastName  
5.    AND c.Country = e.Country;  
6.   
7. SELECT c.LastName, c.Country  
8. FROM dbo.Customer c  
9. WHERE EXISTS (SELECT * FROM dbo.Employee  
10.    WHERE LastName = c.LastName  
11.     AND Country = c.Country);  
12.   
13. SELECT LastName, Country  
14. FROM dbo.Customer   
15. INTERSECT  
16. SELECT LastName, Country  
17. FROM dbo.Employee ;  

SELECT c.LastName, c.Country
FROM dbo.Customer c
 INNER JOIN dbo.Employee e
  ON c.LastName = e.LastName
   AND c.Country = e.Country;

SELECT c.LastName, c.Country
FROM dbo.Customer c
WHERE EXISTS (SELECT * FROM dbo.Employee
   WHERE LastName = c.LastName
    AND Country = c.Country);

SELECT LastName, Country
FROM dbo.Customer 
INTERSECT
SELECT LastName, Country
FROM dbo.Employee ;

What would be the output of each query?

• Statement with INNER JOIN returns only one record belonged to Jack other two queries return records related to both Jack and Brown.
• Statements with INNER JOIN and EXISTS return one record related to Jack and statement with INTERSECT returns records related to both Jack and Brown.
• All statements return records related to both Jack and Brown.
• All statements return only record related to Jack.

For answering this, you need to understand how NULL values are compared with JOINs, predicates and set operators. Generally Set Operators consider two NULL values as equal but comparison with other methods yield unknown. Considering this fact, the correct answer is, second one because INNER JOIN and EXISTS return only one record but INTERSECT returns two records including the record that has NULL for country.