Select records from one table which do not exists in another table

To get the records that do not exist in another table, we can do it either by using left join, not exists or not in queries. let's see an example below

create table customers(
    id int identity(1,1) primary key,
    first_name varchar(40) not null,
    last_name varchar(40),
    email varchar(100) not null,
    active bit not null default(1)
);

insert into customers(first_name, last_name, email) values 
('Ken', 'Sanchez', 'ken@Sanchez.com'),
('Terri', 'Duffy', 'terri@duffy.com'),
('Roberto', 'Tamburello', 'rob@tam.com'),
('Rob', 'Walters', 'rob@walters.com'),
('Gail', 'Erickson', 'gail@eric.com'),
('Jossef', 'Erickson', 'jossef@eric.com'),
('Dylan', 'Miller', 'dylan@miller.com'),
('Diane', 'Margheim', 'diane@margheim.com');

select * from customers; -- returns all customers

create table customer_orders(
    id int identity(1,1),
    customer_id int, 
    order_id int not null,
    active bit not null default(1),
    foreign key (customer_id) references customers(id)
);

insert into customer_orders(customer_id, order_id) values (1, 3), (1, 4), (3, 5), (4, 6), (7, 7);

select * from customer_orders; -- this table contains mapping for each customers with their respective order_ids from orders table.

Here, we want to get the records that are in the customers table but not in the customer_orders table. If the customer doesn't have any data in the customer_orders table then, it means that the customer hasn't placed any orders yet. To get all those customers who haven't placed any orders yet, we will do both ways below firstly by using not exists and secondly using left join.

By using NOT EXISTS:

select * from customers where not exists (select * from customer_orders where customer_orders.customer_id = customers.id);

In the above query, we used not exists in the where clause and in a subquery, we return records that are present in the customer_orders table and customers table. Since we are using not exists, we will get customers' records which do not exists in the customer_orders table;

By using LEFT JOIN:

select 
    c.*
from customers c
left join customer_orders co
    on c.id = co.customer_id
where co.id is null;

In the above query, we used left join which will select all rows from the first table i.e. customers and for each customer, we find rows in the customer_orders table i.e. second_table, the query will return column values from these rows will combine and then include in the resultset. if a customer does not have any matching row in the customer_orders table, the query will return column values from the customers table of that row, with NULL values for each column of the customer_orders table. Since we want customers who are not present in the customer_orders table, we will pick on those rows whose customer_order is null.