SQL Tutorial - EXISTS

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

If you like the video you can support me on Patreon, https://www.patreon.com/beardeddev

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');

Видео SQL Tutorial - EXISTS канала BeardedDev