As an alternative to the joins described in a previous post, "subqueries" can often be used. A subquery is essentially an entire query embedded in another query. Subqueries can be used with UPDATE, INSERT and DELETE statements, whilst joins cannot. However, joins can be used to bring columns from multiple tables. There are no special keywords involved in creating a subquery. There is some discussion on the pros and cons of subqueries and joins here on Stackoverflow.
SET gender = 'f'
WHERE firstname IN (SELECT firstname
WHERE gender = 'f');
The inner query is the clause in brackets, in this instance it is a shorthand way of building a list for an "IN" comparison. Often an inner query returns a single value, i.e. for an average or maximum in a list.
This expression contains a correlated subquery:
SET party_id = (SELECT party_id
WHERE raw_candidate_data.party = parties.party_name);
The inner query requires information from the outer query to work, this expression acts as a look up.
Complex queries can be given aliases using the VIEW keyword, for example:
CREATE VIEW web_designer AS SELECT mc.first_name, mc.last_name, mc.phone,
mc.email FROM my_contacts mc NATURAL JOIN job_desired jd WHERE jd.title='Web Designer';
Can subsequently be used by:
SELECT * FROM web_designers;
The view web_designers can be treated just as any other table.
The results of multiple select commands can be combined using the keywords: UNION, UNION ALL, INTERSECT and EXCEPT. UNION returns the distinct union of the results of all the selects, i.e. with no repeats, UNION ALL includes the repeats. INTERSECT returns items that are common to both SELECTs and EXCEPT returns those items that are returned by the first SELECT but not the second. The general form for these combination operations is as follows:
SELECT title FROM job_current
SELECT title FROM job_desired
SELECT title FROM job_listings
ORDER BY title;
Each SELECT statement must return the same number of columns and each column must be coercible to the same datatype. Only a single ORDER BY statement for the set of SELECTs can be used.
Keywords: UNION, UNION ALL, INTERSECT, EXCEPT, VIEW