The way to use EXISTS and NOT Exists in SQL? Instance Question and Tutorial

Now the second half is how do you examine if there’s a document or not for a selected buyer?  That is the place EXISTS and NOT EXISTS clause will show you how to, and that is what you’ll be taught on this article.

That is truly additionally a preferred SQL Interview query, and also you may need seen it already, however in case you are not, that is superb. This query will not be as in style because the second-largest wage question, however it is usually one of many steadily requested SQL queries from a programming interview; you’ll be able to see the total checklist right here.

Whereas I agree that this drawback will be solved otherwise, however it is usually an ideal instance of how you should use the SQL EXISTS clause.

However, in case you are new to the SQL world, it is higher to start out with a complete SQL course like The Full SQL Bootcamp course by Jose Portilla on Udemy. That can show you how to to be taught SQL higher and faster, and these sorts of articles will even make extra sense after getting some SQL data beneath your belt.

SQL tables and Knowledge for Question Instance

Earlier than wanting on the question, let’s examine the schema, tables, and knowledge to grasp the issue higher. We’ve two tables Prospects and Orders. Prospects comprise two columns, Id and Title. The id is a numeric column whereas the Title is a textual content column, let’s assume of sort VARCHAR.

If any buyer has ordered something, then their CustomerId will exist within the Orders desk, we’ll benefit from this data to resolve this drawback.

We’ve 4 prospects with Id starting from 1 to 4. Our second desk, Orders, incorporates Id, which is a novel id for order, and CustomerId, which is the Id of the Buyer who makes that order. If any Buyer will place an order, then their Id will exist within the Orders desk.

Desk: Prospects.

+—-+——-+
| Id | Title  |
+—-+——-+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+—-+——-+

Desk: Orders.

+—-+————+
| Id | CustomerId |
+—-+————+
| 1  | 3          |
| 2  | 1          |
+—-+————+

Utilizing the above tables for instance, return the next:

+———–+
| Prospects |
+———–+
| Henry     |
| Max       |
+———–+

Should you searching for some extra SQL challenges, then you’ll be able to attempt fixing issues given in Joe Celko’s traditional ebook, SQL Puzzles, and Solutions, the 2nd Version. Probably the greatest books to enhance your SQL question expertise.

The way to Discover Prospects Who By no means Order utilizing EXISTS in SQL

Probably the most widespread options to this drawback is through the use of the SQL JOIN clause. You should use the LEFT OUTER JOIN to resolve this drawback, as proven under:

SELECT C.Title FROM Prospects C
LEFT JOIN Orders O ON  C.Id = O.CustomerId
WHERE O.CustomerId is NULL

While you be part of two tables in SQL utilizing a LEFT OUTER JOIN, then a giant desk will probably be created with NULL values within the column which do not exist in one other desk.

For instance, the massive desk may have 4 columns C.Id, C.Name, O.Id, and O.CustomerId, for Prospects who’ve by no means ordered something, the O.CustomerId will probably be NULL.

Many programmers make the error of utilizing != within the JOIN situation to resolve this drawback, with the idea that if = returns matching rows, then != will return these ids which aren’t current in one other desk. So watch out for that.

Should you wrestle to grasp be part of, then I counsel you check out Jose Portilla’s wonderful SQL course The Full SQL Bootcamp on Udemy. His instructing model is superb and you’ll perceive joins very quickly.

Anyway, this drawback is definitely a wonderful instance of how and when to make use of EXISTS clause:

SELECT C.Title FROM Prospects C 
WHERE NOT EXISTS (SELECT 1 FROM Orders O WHERE C.Id = O.CustomerId)

It is a correlated subquery, the place the internal question will execute for every row of the outer question, and solely these prospects will probably be returned who haven’t ordered something.

Btw, the simplest resolution is through the use of the NOT IN Clause.

SELECT A.Title FROM Prospects A
WHERE A.Id NOT IN (SELECT B.CustomerId FROMs Orders B)

And, here’s a good screenshot to recollect how and when to make use of the EXISTS clause in SQL question: