Home / Tutorial and Knowledge base / MySql tutorial / exists

    MySQL - EXISTS Condition



    In MySQL SQL, you may use the EXISTS condition combined with a subquery in the DML (SELECT, INSERT, UPDATE, DELETE) statement. In EXISTS condition, if the subquery returns any row, it will conside the condition to be met, and the DML, for example, the SELECT statement will return the results.

    In this tutorial, you will learn how to use the "EXISTS" condition In MySQL SQL statement. We will walkthrough the "EXISTS" syntax with detailed explanations and examples.

    Syntax


    The syntax of a the EXISTS condition is as follows:

    SELECT
    column1, column2....

    FROM
    table_name

    WHERE EXISTS
    ( subquery )
    ;


    Parameters Explanations

    subquery
    The subquery is a SELECT statement. If the subquery returns at least one record in its result set, then the EXISTS condition is considered to be fullfilled (true). In that case, the SELECT query will returns all the result which matched the condition




    Examples - How to use the "EXISTS" Condition with the SELECT Statement


    In this tutorial, we will use the well-known Northwind sample database for demonstration purpose. Consider we have the following tables:

    purchase_order table with the following data:

    idcreation_datestatus_id
    1112006-03-31 00:00:00.0002
    1402006-04-25 16:40:51.0002
    1412006-04-25 17:10:35.0002
    1422006-04-25 17:18:29.0002
    1462006-04-26 18:26:37.0001
    1472006-04-26 18:33:28.0001
    1482006-04-26 18:33:52.0001

    purchase_order_status table with the following data:

    idstatus
    0New
    1Submitted
    2Approved
    3Closed


    Here is the example of selecting the purchase_order table with the EXIST condition


    SELECT id, creation_date, status_id
    FROM purchase_orders
    WHERE EXISTS (SELECT * FROM purchase_order_status
    WHERE id = 3)


    The result is:
    idcreation_datestatus_id
    1112006-03-31 00:00:00.0002
    1402006-04-25 16:40:51.0002
    1412006-04-25 17:10:35.0002
    1422006-04-25 17:18:29.0002
    1462006-04-26 18:26:37.0001
    1472006-04-26 18:33:28.0001
    1482006-04-26 18:33:52.0001

    In the above example, since the subquery in the EXISTS condition is met. so all the records of the purchase_orders table has been returned.

    Alternatively, if we changed the subquery condition and make it to false (not met):


    SELECT id, creation_date, status_id
    FROM purchase_orders
    WHERE EXISTS (SELECT * FROM purchase_order_status
    WHERE id = 4)


    Since the subquery condition is not met. The above query would return no record.

    Examples - How to use the "EXISTS" Condition where matching the subquery with the SELECT statement



    Now, we can explore more of how to use the "EXISTS" Condition and match the subquery with the SELECT statement. Consider to use the purchase_orders and purchase_order_status tables again.

    Now, if we issue the following SQL statement:

    SELECT pos.id, pos.status
    FROM purchase_order_status pos
    WHERE EXISTS (SELECT * FROM purchase_orders po
    WHERE po.status_id = pos.id)


    The result is:
    idstatus
    1Submitted
    2Approved


    In the above example, we select the records from purchase_order_status with the id ONLY exists in the table: purchase_orders Since only, status id 1 and 2 exists. So only those records fullfil this condition will be selected from: purchase_order_status








Time and Weather

Calculators

Converters

Online Tools

Tutorial and Knowledge Base

Search the site