EXISTS

MySQL

EXISTS(subquery);

• subquery: This is a SELECT query that returns a result set. EXISTS(subquery) will evaluate as true if the subquery returns at least one line.

Example

SELECT name
FROM employees
WHERE EXISTS (SELECT *
              FROM department
              WHERE employees.dept_id = department.id);

Output

| name  |
|-------|
| John  |
| Emily |
| Paul  |

Explanation

The EXISTS function in this SQL query checks whether any row returned by the subquery matches a condition. If there exists a department with a matching department id for an employee, the employee’s name is returned. In the output, it shows the name of all employees who are part of any department.

PostgreSQL

EXISTS(subquery)

• subquery: The subquery is a SELECT statement that is used within another SQL statement. When used with EXISTS, the subquery returns a Boolean value of true or false. If the subquery results in one or more records, EXISTS returns true. If the subquery generates no records, EXISTS returns false. It’s important to note, EXISTS only checks for the existence of records, it does not concern itself with the actual data contained in those records.

Example

SELECT product_name
FROM products
WHERE EXISTS (
  SELECT 1
  FROM order_items
  WHERE products.product_id = order_items.product_id
);

Output

 product_name
---------------
 Product A
 Product B
 Product C
(3 rows)

Explanation

In this example, the EXISTS function is used to select all the product names from the products table that have at least one matched product id in the order_items table. If there is at least one record in the order_items table that meets the condition, the EXISTS function returns true and the respective product name is retrieved.

SQL Server

EXISTS(subquery)

• subquery: A SELECT statement that returns a result set. The EXISTS function evaluates true or false based on whether this subquery returns any rows.

Example

SELECT CustomerName
FROM Customers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierID = Customers.SupplierID AND Price < 20);

Output

| CustomerName |
| --- |
| John Doe |
| Jane Doe |

Explanation

The SELECT statement returns the name of the customers who supply at least one product priced less than 20. The EXISTS operator stops processing and returns true at the first occurrence where the subquery condition is met, making it more efficient in such cases.

Oracle

EXISTS(subquery)

• subquery: A SELECT statement enclosed in parentheses that returns a result set. With the EXISTS condition, the database engine checks for the existence of rows in this result set. It returns TRUE if the subquery returns at least one record, otherwise, it returns FALSE.

Example

SELECT employee_name
FROM employee
WHERE
EXISTS (
  SELECT 1
  FROM employee_projects
  WHERE employee.employee_id = employee_projects.employee_id
  AND project_id = 10001);

Output

EMPLOYEE_NAME
----------
John Doe
Jane Smith

Explanation

The EXISTS function in the query returns true if the subquery returns at least one record; otherwise, it returns false. The subquery checks for each employee if there is a record in the employee_projects table where the project_id equals 10001. If such a record exists, the main query selects the employee_name of the respective employee. In this example, the employees John Doe and Jane Smith are working on the project with the ID 10001.

SQLite

EXISTS(subquery)

• subquery: A SELECT statement that is enclosed in parentheses. The EXISTS function uses this subquery to test for the existence of rows in a database that meet the conditions specified in the subquery. It returns TRUE if the subquery returns one or more records; otherwise, it returns FALSE.

Example

CREATE TABLE Employees (
    ID int,
    Name text,
    City text
);

INSERT INTO Employees (ID, Name, City)
VALUES (1, 'John Doe', 'Chicago'),
       (2, 'Jane Doe', 'New York'),
       (3, 'Alice', 'Los Angeles');

SELECT * FROM Employees E
WHERE EXISTS (SELECT 1 FROM Employees WHERE City = 'Chicago' AND ID = E.ID);

Output

ID | Name    | City
---|---------|---------
1  | John Doe| Chicago

Explanation

The EXISTS function is used in the SQL query to check if any rows are returned by the subquery. In this example, EXISTS checks if there are any employees living in Chicago. It returns true when the subquery returns at least one record, which in this case is the employee named John Doe who lives in Chicago. Therefore, only the record of John Doe is shown in the output.