SQL Tutorial - EXISTS

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

In this SQL tutorial we take a look at exists, how to use exists with subqueries, how to create a correlated subquery and the difference between exists and in/not in. Exists can be used when we want to return results from one set based on whether the data exists in another set. Exists is not a replacement for joins and in the tutorial I discuss why, there are times when working with not in and NULL exists in the data that the results we are expecting are not returned but when using exists the results are what we expect.

If you would like to follow along with this SQL tutorial then you can use the below SQL to create the necessary objects.

Please feel free to post any comments.

DROP TABLE IF EXISTS dbo.T1;

CREATE TABLE dbo.T1
(
col1 TINYINT NULL
);

DROP TABLE IF EXISTS dbo.T2;

CREATE TABLE dbo.T2
(
col1 TINYINT NULL
);

INSERT INTO dbo.T1 (col1) VALUES (1), (2), (3);

INSERT INTO dbo.T2 (col1) VALUES (1), (2), (NULL);

SELECT * FROM dbo.T1;

SELECT * FROM dbo.T2;

DROP TABLE IF EXISTS dbo.Employees;

CREATE TABLE dbo.Employees
(
EmpId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Position VARCHAR(50) NOT NULL
);

DROP TABLE IF EXISTS dbo.Customers;

CREATE TABLE dbo.Customers
(
CustId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL
);

INSERT INTO dbo.Employees (FirstName, LastName, Position)
VALUES
('Deidre', 'Walsh', 'Sales Assistant'),
('Matthew', 'Arlington', 'Sales Assistant'),
('Michelle', 'Montgomery', 'Sales Assistant'),
('Lee', 'Chen', 'Sales Assistant');

INSERT INTO dbo.Customers (FirstName, LastName)
VALUES
('Deidre', 'Walsh'),
('Raphael', 'Jones'),
('Lee', 'Chen');

[surajification]

BeardedDev- Really love how you go about breaking down concepts in your videos. Kudos to you.

[johnstephens]

I really appreciate the way you explain things. Super helpful video.

[redorange]

13:00 It's possible in PostgreSQL:
SELECT *
FROM employees
WHERE (first_name, last_name) IN (
SELECT first_name, last_name
FROM customers
)

[MattSharpe]

Thanks, nice to see a simple explanation :)

[ciencia]

I don't understand the reason why in the minute: 7:24, the table has only values 1 and 2.

I understood from what you said before (minute 1:35) that "Exists" returns true or false (in general) when subquery returns minimum a row, therefore I would expect it to show all the values from dbo.t1 (1, 2, 3), because the subquery returns at least a row (as there are two values from A.col1 that equals two values from B.col1), ending up making "Exists" True.

What's wrong with my logic?

[superfreiheit]

I need to use this in the EXISTS clause
AND e.FirstName = c.FirstName
AND e.LastName = c.LastName
Then the SQL server check every whetever the firstname and the lastname in the first table also exists in second table. If yes, then this row will be shown?

[jeffbezos]

What does select 1, select NULL means?