Difference between revisions of "Subquery and JOIN"
| Line 34: | Line 34: | ||
<ul>Use the following steps to change a subquery into a <code>JOIN</code> | <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 <code>FROM</code> clauses, use aliases</li> |
| − | <li>Move all FROM statements together to form a single FROM </li> | + | <li>Move all <code>FROM</code> statements together to form a single <code>FROM</code> </li> |
<li>Delete all occurrences of (<code>SELECT</code> </li> | <li>Delete all occurrences of (<code>SELECT</code> </li> | ||
<li>Substitute <code>WHERE</code> for AND after the first occurence of <code>WHERE</code></li> | <li>Substitute <code>WHERE</code> for AND after the first occurence of <code>WHERE</code></li> | ||
| Line 44: | Line 44: | ||
AND ranks.title = jobs.title | AND ranks.title = jobs.title | ||
AND jobs.employee = 'Andrew Cumming'</source> | AND jobs.employee = 'Andrew Cumming'</source> | ||
| − | <div class="ecomm e-mysql" style="display: none">JOIN ON functions would also work with | + | <div class="ecomm e-mysql" style="display: none"><code>JOIN ON</code> functions would also work with |
SELECT payment | SELECT payment | ||
FROM salary JOIN ranks ON (salary.rank = ranks.rank) | FROM salary JOIN ranks ON (salary.rank = ranks.rank) | ||
Revision as of 13:34, 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
FROMclauses, use aliases - Move all
FROMstatements together to form a singleFROM - 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
