Sub Queries In SQL

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');

Translate »