AppDividend
Latest Code Tutorials

SQL EXISTS OPERATOR Tutorial With Example

0

SQL EXISTS OPERATOR Tutorial With Example is today’s topic. The SQL exists operator is used for checking the existence of a record in any subquery. The SQL exists condition is used in the combination with a subquery and is met if the subquery results in any one row. The result of exists keyword is a Boolean value, i.e., True or False. It can be used with select, insert, update, or delete in a query.

SQL EXISTS OPERATOR

The EXISTS operator is used to test for existence of any record in the subquery. The EXISTS operator returns true if a subquery returns one or more records. The EXISTS condition in SQL is used to check whether a result of a correlated nested query is empty (contains no tuples) or not. 

  1. WHERE EXISTS tests for the existence of any records in the subquery.
  2. EXISTS returns true if a subquery returns one or more records.
  3. EXISTS is commonly used with a correlated subquery.

See the following syntax of SQL Exists Statement.

#SYNTAX

Select column(s) from table_name  
where exists
(Select column_name 
from table_name 
where condition);

#PARAMETERS

  1. Column(s) is the name of the column in the table.
  2. Table_name is the name of the table.
  3. Where exists is the condition is used for checking the exists of record in a subquery.

Also, there is another syntax which is similar to the above.

SELECT [Column Names]
FROM [Source]
WHERE EXISTS (Write Subquery to Check)
  1. Columns: It allows us to choose a number of columns from the tables. It may be one or more columns.
  2. Source: One or more sql tables present in the Database. SQL JOINS are used to join the multiple tables.
  3. Subquery: Here, we have to provide a Subquery. If a subquery returns true, then it will return the rows; otherwise, it doesn’t return any records.

Let’s understand this by examples:

Consider table: (CUSTOMERS and ORDERS)

CUSTOMERS:

CUST_CODE CUST_NAME CITY GRADE AMOUNT
101 SHUBH KOLKATA 1 10000
201 SOURAV KOLKATA 1 10000
301 KISHAN PATNA 2 20000
401 DILEEP ALLAHABAD 2 30000
501 SHRUTI PATNA 3 40000
601 SHOUVIK JALANDHAR 4 20000
701 ROHIT PATNA 2 40000
801 ROUNAK NULL 5 10000

 

ORDERS:

O_id Cust_id Order_date
1 201 2019-02-06
2 301 2019-03-06
3 302 2019-04-06
4 403 2019-05-06

 

#Using EXISTS condition with a select statement

In this, we are going to use the exists condition within a select statement.

#QUERY

select cust_name, city 
from customers 
where exists
(select * from orders 
where customers.cust_code = orders.cust_id);

See the output.

 

Using EXISTS condition with a select statement

#EXPLANATION

In the above query, we had fetched the name and city of the customers whose orders were placed, and this has been implemented using exists condition.

#Using NOT EXISTS condition with select statement

In this we are going to use not exists condition within a select statement.

#QUERY

select cust_name, city 
from customers 
where not exists
(select * from orders 
where customers.cust_code = orders.cust_id);

See the following output.

 

Using NOT EXISTS condition with select statement

#EXPLANATION

In the above query, we had fetched the name and city of the customers whose orders were not placed, and this has been implemented using exists condition.

#Using Exists condition with DELETE statements

In this we are going to use the exists condition with delete statements.

#QUERY

Delete from orders 
where exists
(select * from customers 
where customers.cust_code = orders.cust_id 
AND customers.cust_name='Kishan');

See the following output.

 

Using Exists condition with DELETE statements

#EXPLANATION

In the above query, we have deleted the record of order from orders table whose name is Kishan in Customers table.

#Using Exists condition with UPDATE statements

In the above query, we have updated the name of the customer whose customer id is 201 in the orders table.

#QUERY

Update customers 
set cust_name='Pranav' 
where exists
(select * from orders 
where customers.cust_code = orders.cust_id);

See the following output.

 

SQL EXISTS OPERATOR Tutorial Example

#EXPLANATION

In the above query, we have updated the name of the customer whose customer id is 201 in the orders table.

#NOTE

SQL statements that use the EXISTS condition are very inefficient since the sub-query is rerun for every row in the outer query’s table.

Finally, SQL EXISTS OPERATOR Tutorial With Example is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.