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:
- A subquery must always
appear within parentheses.
- 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.
- Subqueries can be used
that return more than one row with multiple value operators, such as the IN or
NOT IN operator.
- 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