UGC NET CSE | June 2009 | Part 2 | Question: 21

Question

Which construct in SQL is used to test whether a subquery has any tuples in its result ?

• A. UNIQUE                   
• B. EXISTS     
• C. GROUP BY             
• D. EXCEPT

Answer 1

(A) UNIQUE  :The UNIQUE constraint ensures that all values in a column are different.

(B) EXISTS   : SQL EXISTS operator to test if a subquery returns any rows.

(C) GROUP BY :   GROUP BY clause to group rows into a set of summary rows by the values of columns or expressions..

(D) EXCEPT : The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Option B is Correct Answer.

Answer 2

Example - Using EXISTS Condition with the SELECT Statement

Let's start by looking at an example that shows how to use the EXISTS condition with a SELECT command

In this example, we have a customers table with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

And a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20

Now let's find all of the records from the customers table where there is at least one record in the orders table with the same customer_id. Enter the following SELECT statement:

SELECT *
FROM customers
WHERE EXISTS 
  (SELECT *
   FROM orders
   WHERE customers.customer_id = orders.customer_id);

There will be 4 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL

In this example, there are 4 records in the customers where the customer_id value appears in the orders table.