Tuesday, June 25, 2024
HomeJavaThe way to use EXISTS and NOT Exists in SQL? Instance Question...

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


Whats up Guys, you may need heard about how helpful the EXISTS clause is useful in writing subtle queries. Nonetheless, on the similar time, I’ve additionally seen that many programmers wrestle to grasp and use EXISTS and NOT EXISTS clauses whereas writing SQL queries.  In case you are considered one of them, then you’ve come to the precise place. Immediately you’ll discover ways to use the EXISTS clause in SQL by selecting up a real-world instance and a very good

from the LeetCode. Suppose {that a} web site incorporates two tables, the

desk.

To resolve this drawback, it’s worthwhile to first take into consideration knowledge. For instance, if a buyer has by no means ordered something means there will not be any document for him on the Orders desk. Good job, half of the duty is completed.

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:

How to use EXISTS Clause in SQL? A Real World SQL Query Example using EXISTS
That is all about the right way to use the EXISTS clause in SQL to seek out all prospects who’ve by no means ordered. Should you like to enhance your SQL question expertise, then it’s also possible to issues given in Joe Celko’s classical ebook SQL Puzzles and Solutions, the 2nd Version. Probably the greatest books with a number of difficult questions to check your SQL talent, and should you want some on-line programs to be taught SQL in-depth or fill the gaps in your SQL data, the next programs are place to start out with.

Different associated SQL queries, Interview questions, and articles:

  • The way to be part of three tables in a single single SQL question (resolution)
  • Write a SQL question to seek out all desk names on a database in MySQL (resolution)
  • 5 Programs to be taught Database and SQL Higher (programs)
  • Write a SQL question to repeat or backup a desk in MySQL (resolution)
  • How do you discover the duplicate rows in a desk on a database? (resolution)
  • 5 Programs to be taught Oracle and Microsoft SQL Server database (programs)
  • The actual distinction between WHERE and HAVING clause in SQL? (reply)
  • The way to migrate SQL queries from Oracle to SQL Server 2008? (reply)
  • Prime 5 Web sites to be taught SQL on-line for FREE? (useful resource)
  • What’s the distinction between UNION and UNION ALL in SQL? (reply)
  • Distinction between Self and Equi Take part SQL? (reply)
  • Prime 5 Programs to be taught MySQL Database for Newbies (Programs)
  • What’s the distinction between View and Materialized View in Database? (reply)
  • Distinction between clustered and non-clustered indexes in SQL? (reply)
  • Distinction between Major and Candidate key in desk? (reply)
  • 5 Free Programs to be taught T-SQL and SQL Server for Newbies (Programs)
  • Distinction between Distinctive and Major key in desk? (reply)
  • Distinction between Major and International key in desk? (reply)

Thanks for studying this text, should you like this SQL EXISTS tutorial and instance, then please share it with your folks and colleagues. When you’ve got any questions or suggestions, then please drop a notice.

P.S. – In case you are involved in studying Database and SQL and searching for some free sources to start out your journey, then it’s also possible to check out this checklist of Free SQL Programs for Newbies to kick-start your studying.



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments