Syntax for using EXISTS and NOT EXISTS in SQL

The usage of exists and not exists in SQL requires specific code examples

In SQL, exists and not exists are a pair of commonly used predicates (predicate) , used to determine whether a subquery returns a result set. exists is used to check whether the subquery returns at least one row of results, while not exists is used to check whether the subquery returns no results. The syntax of

exists is as follows:

SELECT column1, column2, ...
FROM table
WHERE EXISTS (subquery);

The syntax of not exists is as follows:

SELECT column1, column2, ...
FROM table
WHERE NOT EXISTS (subquery);

The following uses several specific examples to introduce the usage of exists and not exists.

Example 1: Find records with specific conditions

Suppose we have a table named "employees" that contains employee information, including "employee_id", "first_name" and "last_name" "Waiting for a row. We want to find all employee information for which the position "manager" exists.

SELECT employee_id, first_name, last_name
FROM employees
WHERE EXISTS (SELECT 1
              FROM employees
              WHERE job_title = 'manager'
                AND employees.employee_id = manager_id);

The subquery in the above code retrieves records with the position of "manager" from the "employees" table. The outer main query uses the exists predicate to determine whether the subquery returns at least one row of results.

Example 2: Find records that do not have specific conditions

Suppose now we want to find information about employees who do not have the position of "manager".

SELECT employee_id, first_name, last_name
FROM employees
WHERE NOT EXISTS (SELECT 1
                  FROM employees
                  WHERE job_title = 'manager'
                    AND employees.employee_id = manager_id);

The subquery in the above code also retrieves records with the position of "manager" from the "employees" table. The outer main query uses the not exists predicate to determine whether the subquery returns no results.

Example 3: Comparison of subqueries

Subqueries that use exists and not exists as predicates can also be filtered using other conditions.

Suppose we want to find information about employees with the highest salary.

SELECT employee_id, first_name, last_name
FROM employees e1
WHERE NOT EXISTS (SELECT 1
                  FROM employees e2
                  WHERE e2.salary > e1.salary);

In the above code, the subquery retrieves records whose salary is greater than the current employee, and uses the not exists predicate to determine whether there are no records that meet the conditions.

Summary:

exists and not exists are commonly used predicates in SQL, used to check whether a subquery returns a result set. exists is used to determine that at least one row of results exists, and not exists is used to determine that there is no result. By using exists and not exists, we can write more flexible query statements to meet different business needs.

The above is the detailed content of Syntax for using EXISTS and NOT EXISTS in SQL. For more information, please follow other related articles on the PHP Chinese website!