Question

Exists & notexists are usually used in conjunction with

Answer 1

Explanation:

Using EXISTS and NOT EXISTS in correlated subqueries

EXISTS and NOT EXISTS are used with a subquery in WHERE clause to examine if the result the subquery returns is TRUE or FALSE. The true or false value is then used to restrict the rows from outer query select. Because EXISTS and NOT EXISTS only return TRUE or FALSE in the subquery, the SELECT list in the subquery does not need to contain actual column name(s). Normally use SELECT * (asterisk) is sufficient but you can use SELECT column1, column2, ... or anything else. It does not make any difference.

Because EXISTS and NOT EXISTS are used with correlated subqueries, the subquery executes once for every row in the outer query. In other words, for each row in outer query, by using information from the outer query, the subquery checks if it returns TRUE or FALSE, and then the value is returned to outer query to use.

Practice #1: Using EXISTS in correlated subquery.

By examining the query in this practice, we can sum up the following steps that the database engine takes to evaluate the correlated subquery. It demonstrates that the subquery uses data from the outer query and the subquery executes once for every row in the outer query.

The outer query passes a value for CustomerID to the subquery. It takes place in the WHERE clause in the subquery.

The subquery uses this passed-in CustomerID value to look up ShipCountry of UK in orders table.

When a matched row is found, the subquery returns the value TRUE to the outer query.

The outer query returns the CustomerID and CompanyName for this row in Customers table.

The query engine then moves onto next row in the Customers table and repeat Step 1 to 4 again for the next customer.

When all customers in Customers table have been evaluated, it returns the query result.

Copy and paste the following SQL to your SQLyog free Community Edition query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you