Which of the following DML SQL statements support usage of correlated sub-queries?
Each correct answer represents a complete solution. Choose all that apply.
A.
INSERT
B.
UPDATE
C.
DELETE
Explanation:
A correlated sub-query is a sub-query where a column of a table referred in the outer parent query is
referenced in the sub-query. Two tables are involved in a correlated sub-query and both have data in
them. The two tables are linked to each other using a common column. In the UPDATE and DELETE
SQL statements the table can have data and can be linked with a common column. However, the
same is not true for INSERT statements as in the INSERT statement the table does not contain any
data, and hence, cannot be correlated with the inner query table.
The best way to work with correlated sub-queries is to use table and/or column aliases to clearly
understand which columns are being referenced from which tables. Without aliases Oracle will
return an ambiguity error in some cases.
Answer A is incorrect. The INSERT statements do not support correlated sub-queries. They support
other sub-queries like INSERT INTO (SELECT FROM ).
Correlated sub-queries with INSERT will not work because for a correlated sub-query to work there
has to be a common column based on which rows in the table are linked to the columns of the table
used in the sub-query. When using the INSERT state, the table does not contain any data; hence, a
correlation cannot occur. You may however, INSERT data first into a table using INSERT INTO (SELECT
FROM ) and then use UPDATE statements and correlated sub-queries to update the data from other
tables.