***Welcome to ashrafedu.blogspot.com * * * This website is maintained by ASHRAF***

Posts

    Tuesday, 17 December 2019

    Nested Queries


    Nested Queries

    A nested query, also known as a subquery or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

    Nested queries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions. The most important ones are listed below:

    1.          A subquery must always appear within parentheses.
    2.          A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring has only one column. Subquery may be used that returns multiple columns, if the purpose is row comparison.
    3.          Subqueries can be used that return more than one row with multiple value operators, such as the IN or NOT IN operator.
    4.          A subquery cannot be a UNION. Only a single SELECT statement is allowed.

    ·         A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

    Subqueries with the SELECT Statement

    Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows
    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE column_name OPERATOR (SELECT column_name [, column_name ]
       FROM table1 [, table2 ]  [WHERE]);

    Example:
    SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS
    WHERE SALARY > 4500) ;

    Subqueries with the INSERT Statement

    The INSERT statement uses the data returned from the subquery to insert into another table.
    The basic syntax is as follows:
    INSERT INTO table_name [ (column1 [, column2 ]) ]
       SELECT [ * |  column1 [, column2 ]
       FROM table1 [, table2 ]
       [ WHERE VALUE OPERATOR ];

    Example:
    INSERT INTO CUSTOMERS_BKP (SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS) ;

    Subqueries with the UPDATE Statement

    The basic syntax is as follows:
    UPDATE table SET column_name = new_value
    [ WHERE OPERATOR [ VALUE ]
       (SELECT COLUMN_NAME FROM TABLE_NAME)  [ WHERE) ]

    Example:
    UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

    Subqueries with the DELETE Statement

    The basic syntax is as follows:
    DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

    Example:
    DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
          WHERE AGE >= 27);

    No comments:

    Post a Comment