Sub queries are queries inside queries. These sub queries are used in the query to get required details for the main query from some other tables. For example, if we have to see the books which are published by the publishers in Delhi, then we can use sub query like below. We can even do the same using normal joins, but sub queries gives better meaning to the query. Sub queries can be simple to complex with any number of conditions and groupings.
SELECT * FROM BOOKS WHERE PUBLISHER_ID IN (SELECT PUB_ID FROM PUBLISHER WHERE CITY = ‘Delhi’);
Sub queries can be used to insert the records into the table like below. But care should be taken to match the columns datatype and size with the inserting table.
INSERT INTO BOOK_LOAN_MAY2015 SELECT bl.ISBN, bl.BOOK_CP_NUMBER, bl.LOAN_DATE FROM BOOK_LOAN bl WHERE TO_CHAR (LOAN_DATE, 'MONYYYY') = 'MAY2015';
Like insert, sub queries can be used for update and delete too. It can be used here to get the list of values to be updated or deleted.
UPDATE BOOK_RETURNS SET RETURN_DATE = '20-JUN-15' WHERE RETURN_DATE = '24-JUN-15' AND ISBN = (SELECT ISBN FROM BOOKS WHERE BOOK_NAME = 'Organometallic And Bioinorganic Chemistry');
DELETE FROM BOOK_RETURNS WHERE RETURN_DATE = '24-JUN-15' AND ISBN = (SELECT ISBN FROM BOOKS WHERE BOOK_NAME = 'Organometallic And Bioinorganic Chemistry');