31. SQL QUERIES

TIZAG.COM

SQL - Subqueries

MySQL offers a very limited support for subqueries, however Oracle and DB2 fully support them. Subqueries are Select queries placed within an existing SQL statement. They may exist in any of the following types of SQL statements.

• Select
• Insert
• Update
• Delete
• Set
• Do

Subqueries are great for answering very specific questions regarding your data inside your database. For instance, as the employer you may notice employee number 101 had a great day yesterday with sales. Just given this information we can use a subquery to pull the employee lastname and first name from our database.

SQL Code:

SELECT * FROM employees
WHERE id =
(SELECT EmployeeID FROM invoices WHERE EmployeeID='1');

SQL Table:

id	Lastname	Firstname	Title
11	Davis	Julie	MANAGER

Here we have pulled our employee information from the employees table by only knowing the employee number from the invoices table.

SQL - Subquery Inserts

Subqueries can be used to pull old data from your database and insert it into new tables. For instance if we opened up a third store and we wanted to place the same manager over 3 stores we could do this by pulling the manager's information using a subquery and then inserting the records. Also note that this form of insert will insert all cases where the subquery is true, therefore several rows may or may not be inserted depending upon how your table is set up.

SQL Code:

INSERT INTO employees3
(id,Lastname,Firstname,Title)
(SELECT  id,Lastname,Firstname,Title
FROM employees WHERE Title='manager');

With complete mastery of a subqueries you can now see the power of the SQL language. The language is capable of nearly all things imaginable.