Keywords with Nested Queries

tags: #sql/complex_queries

EXISTS Keyword

EXISTS is a boolean operator that returns either True or False.

• Its used in combination to a sub-query
• The EXISTS keyword returns true if the subquery returns one or more rows that also references the main query, otherwise, it returns false.

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery); // checking if any rows exists in subquery

Example:

SELECT E.Emp_Name, E.Dept_No
FROM Employee AS E
WHERE EXISTS (SELECT P.Pno
              FROM Project AS P
              WHERE P.Emp_SSN = E.Emp_SSN);

• Here, the subquery checks whether there is at least one project that the employee has been assigned to.
• If the subquery returns one or more rows, the EXISTS keyword returns true, and the main query returns the employee's name and department number.
• If the subquery returns no rows, the EXISTS keyword returns false, and the main query returns no rows.
• This essentially look for the existence of a row in a given table that satisfies a set of criteria.

This can be used with

IN Clause

The IN clause retrieves all records which match with the given set of values.

• It acts as multiple OR conditions.
• Scans all rows fetched from the inner query, therefore, less efficient when the subquery is large.
• Use to simplify queries and reduce the use of multiple OR conditions.

SELECT column1, column2, ...
	FROM table_name
	WHERE (condition) in (
		--subqeury
		SELECT cols 
			from table_name
			.... 
			);

What is the equivalent logic for multiple AND conditions?

Consider the following scenario where you want to check that values of an instance of an entity in more than one column (e.g., column1 and column2) from the main query exist in a subquery, you can use the following SQL approaches:

SELECT column1, column2
FROM main_table
WHERE column1 IN (SELECT colx FROM subquery_table)
  AND column2 IN (SELECT coly FROM subquery_table);

This ensures that both conditions are satisfied: the value in column1 must exist in the result of the subquery for colx, and the value in column2 must exist in the result of the subquery for coly.

NOT EXISTS Negation

We can use NOT EXISTS is to return all the rows from the main query where a correlated subquery has no matching rows (also used to express the "for all" condition).

Example:

Want to find all employees who have not been assigned to any project.

SELECT E.name
FROM EMPLOYEE AS E
WHERE NOT EXISTS (
	SELECT E.name
	FROM WORKS_ON AS W
	WHERE W.ESSN = E.ESSN
)

• Here, the subquery returns all the rows from the WORKS_ON table where the employee SSN matches the ESSN in the EMPLOYEE table.
• The outer query then selects all employees whose SSN does not exist in the result set of the subquery, i.e., employees who are not assigned to any project.

UNIQUE Keyword

The UNIQUE keyword is not used in the same way as EXISTS and IN in SQL. Instead, it is typically associated with constraints and indexes to enforce the uniqueness of values in a column or combination of columns.

Example:

SELECT Dname
FROM DEPARTMENT D
WHERE UNIQUE(SELECT Salary -- retrieving UNIQUE result set of salary from E
            FROM EMPLOYEE E
            WHERE E.Dno = D.Dnumber)

• This query returns the name of departments where all employees have unique salaries.
• The UNIQUE keyword ensures that only departments where all employees have unique salaries are returned.