Subquery With EXISTS Operator

If a subquery uses EXISTS, such as testing the same function exists or not. WHERE clause test the existence of the outer query-lines produced by the subquery. Subquery does not return data, but produces the value TRUE or FALSE. Subquery examples that use IN, will be changed by using EXISTS:

SELECT DISTICT FompanyName,PostalCode FROM Customers WHERE EXITS (SELECT * FROM Suppliers WHERE PostalCode = Customers.PostalCode)

If you use exists, it must use SELECT *. Keyword, NOT EXISTS is the opposite of EXISTS, WHERE clause subquery is used to indicate that there is no result.

Adding Data With Subquery

You can use subquery, SELECT in INSERT statement to add values to a table or several tables or views. Subquery allows you to add a few rows at once into a table.

What is the difference subquery usage, with usage VALUES?. Subquery will add a number of existing data, while the VALUES to add new data. For example, we create a new table named TmpPublisher into the pubs database. The table contains four fields and made with command :

CREATE TABLE TmpPublisher
SELECT pub_name varchar, city, state, country FROM Publisher WHERE COUNTRY =’USA’

If the subquery is used in INSERT command, then the subquery is not necessary are enclosed in brackets. Subquery produce the same four columns and fields in the destination table. For example in tanel pub_name TmpPublisher will be filled only for the data not from the USA, then the INSERT clause you should declare a column that will contain the data.

INSERT INTO TmpPublisher (pub_name)
 SELECT pub_name FROM Publisher WHERE NOT COUNTRY = 'USA'