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;
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;
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.
