Difference between revisions of "Subquery and JOIN"
| Line 30: | Line 30: | ||
(SELECT title FROM jobs | (SELECT title FROM jobs | ||
WHERE employee = 'Andrew Cumming')) | WHERE employee = 'Andrew Cumming')) | ||
| − | This subquery would be more efficient if it was changed to a JOIN as there are no | + | This subquery would be more efficient if it was changed to a <code>JOIN</code> as there are no |
aggregate functions in the queries. | aggregate functions in the queries. | ||
| − | <ul>Use the following steps to change a subquery into a JOIN | + | <ul>Use the following steps to change a subquery into a <code>JOIN</code> |
<li>Mark all columns with the table name they came from</li> | <li>Mark all columns with the table name they came from</li> | ||
<li>If you use the same table in two different FROM clauses, use aliases</li> | <li>If you use the same table in two different FROM clauses, use aliases</li> | ||
<li>Move all FROM statements together to form a single FROM </li> | <li>Move all FROM statements together to form a single FROM </li> | ||
| − | <li>Delete all occurrences of (SELECT </li> | + | <li>Delete all occurrences of (<code>SELECT</code> </li> |
| − | <li>Substitute WHERE for AND after the first occurence of WHERE</li> | + | <li>Substitute <code>WHERE</code> for AND after the first occurence of <code>WHERE</code></li> |
</ul> | </ul> | ||
</div> | </div> | ||
Revision as of 14:33, 30 July 2012
Converting subqueries into Join functions.
DROP TABLE jobs; DROP TABLE ranks; DROP TABLE salary;
CREATE TABLE jobs( employee VARCHAR(40), title VARCHAR(40)); INSERT INTO jobs VALUES ('Gordon Russell','Lecturer'); INSERT INTO jobs VALUES ('Andrew Cumming','Teaching fellow'); INSERT INTO jobs VALUES ('Jim Smith','Technician'); CREATE TABLE ranks ( title VARCHAR(40), rank VARCHAR(40)); INSERT INTO ranks VALUES ('Lecturer','LECT1'); INSERT INTO ranks VALUES ('Teaching fellow','LECT2'); INSERT INTO ranks VALUES ('Technician','TECH1'); CREATE TABLE salary ( rank VARCHAR(40), payment INTEGER); INSERT INTO salary VALUES ('LECT1',2000); INSERT INTO salary VALUES ('LECT2',3000); INSERT INTO salary VALUES ('TECH1',5000); INSERT INTO salary VALUES ('TECH2',6000);
SELECT payment FROM salary WHERE rank = (SELECT rank FROM ranks WHERE title = (SELECT title FROM jobs WHERE employee = 'Andrew Cumming'))
This subquery would be more efficient if it was changed to a JOIN as there are no
aggregate functions in the queries.
- Use the following steps to change a subquery into a
- Mark all columns with the table name they came from
- If you use the same table in two different FROM clauses, use aliases
- Move all FROM statements together to form a single FROM
- Delete all occurrences of (
SELECT - Substitute
WHEREfor AND after the first occurence ofWHERE
JOIN
SELECT payment FROM salary, ranks, jobs WHERE salary.rank = ranks.rank AND ranks.title = jobs.title AND jobs.employee = 'Andrew Cumming'
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
