Using EQUALS clause with Subquery

In SQL, the `EQUALS` clause is not used directly as a subquery statement; instead, the equality operator `=` is commonly used within a subquery to compare a value in the outer query to the result of the subquery. A subquery, in this context, is a query nested within another query, and it is used for various purposes such as filtering, evaluating conditions, or defining a value for comparison. When using `=` with a subquery, the subquery must return exactly one value (a single row with a single column) for the comparison to be valid. This is often achieved using aggregation functions or by ensuring the query is constructed in such a way that only one row can possibly be returned. If the subquery returns no rows, the condition evaluates to `FALSE`; if it returns more than one row, SQL returns an error because the equality comparison becomes ambiguous.
Example:
Consider two tables: `Employees` and `Departments`. The `Employees` table contains employee details including their `DepartmentID`, and the `Departments` table contains details about each department.

- Employees table:

| EmployeeID | Name     | DepartmentID |
|------------|----------|--------------|
| 1          | John Doe | 2            |
| 2          | Jane Doe | 3            |

- Departments table:

| DepartmentID | DepartmentName |
|--------------|----------------|
| 1            | HR             |
| 2            | IT             |
| 3            | Finance        |

Suppose you want to find the name of the employee who works in the 'IT' department. The query could be structured using a subquery with the = operator as follows:

SELECT Name
FROM Employees
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'IT'
);

In this example, the subquery:

SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'IT'

returns a single value, which is the DepartmentID corresponding to the 'IT' department. This value is then used by the outer query to filter employees who belong to the 'IT' department. The equality operator = is used to compare the DepartmentID in the Employees table with the result of the subquery, ensuring that only employees from the 'IT' department are selected. This use of the = operator with a subquery enables precise filtering based on the result of an inner query, facilitating complex data retrieval operations in SQL with clear and concise syntax.

Earlier, we mentioned there are two different approaches to SELECT and subquery statements.

1. The first approach is the IN clause, which we have already covered.
2. The second is the EQUALS clause, indicated quite simply with the = sign.

The syntax for the two clauses is nearly identical, with the = sign substituted for the IN phrase:
Example of Subquery

SELECT Title FROM Titles
WHERE pub_id=
(SELECT Pub_ID FROM Publishers 
WHERE State='CA')

There is one requirement if you use this approach. Make sure the subquery statement returns only one value. With the IN clause, you returned a list of values used as a comparison. With the EQUALS approach, there can be one and only one value represented when the results of the subquery are evaluated. The subselect statement can have nearly any additional clauses that you can put on a standard SELECT statement.
You can use WHERE, or you can use the keywords that you are familiar with from your experience with SQL.

A subquery is a query contained within another SQL statement (which I refer to as the containing statement for the rest of this discussion). A subquery is always enclosed within parentheses, and it is usually executed prior to the containing statement. Like any query, a subquery returns a result set that may consist of:

1. A single row with a single column
2. Multiple rows with a single column
3. Multiple rows and columns

The type of result set the subquery returns determines how it may be used and which operators the containing statement may use to interact with the data the subquery returns. When the containing statement has finished executing, the data returned by any subqueries is discarded, making a subquery act like a temporary table with statement scope (meaning that the server frees up any memory allocated to the subquery results after the SQL statement has finished execution). You already saw several examples of subqueries in earlier chapters, but here's a simple example to get started:

mysql> SELECT MAX(account_id) FROM account;
+-----------------+
| MAX(account_id) |
+-----------------+
|       29        |
+-----------------+
1 row in set (0.00 sec)

In this example, the subquery returns the maximum value found in the account_id column in the account table, and the containing statement then returns data about that account. If you are ever confused about what a subquery is doing, you can run the subquery by itself (without the parentheses) to see what it returns.
Here is the subquery from the previous example:

mysql> SELECT MAX(account_id) FROM account;
+-----------------+
| MAX(account_id) |
+-----------------+
|         29      |
+-----------------+
1 row in set (0.00 sec)

The subquery returns a single row with a single column, which allows it to be used as one of the expressions in an equality condition (if the subquery returned two or more rows, it could be compared to something but could not be equal to anything). In this case, you can take the value the subquery returned and substitute it into the righthand expression of the filter condition in the containing query, as in:

mysql > SELECT account_id, product_cd, cust_id, avail_balance
-> FROM account
-> WHERE account_id = 29;
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
|      29    |     SBL    |     13  |    50000.00   |
+------------+------------+---------+---------------+
1 row in set (0.02 sec)

In the next lesson, the details about using the DISTINCT keyword and how you can integrate it into your SELECT (and subquery) statements will be discussed.