Difference between revisions of "Subquery and JOIN"
From SQLZOO
| Line 2: | Line 2: | ||
<div class='ht'> | <div class='ht'> | ||
<div class=params>schema:scott</div> | <div class=params>schema:scott</div> | ||
| − | <source lang=sql class='tidy'></source> | + | <source lang=sql class='tidy'>DROP TABLE jobs, ranks, salary</source> |
<source lang=sql class='setup'> CREATE TABLE jobs( | <source lang=sql class='setup'> CREATE TABLE jobs( | ||
employee VARCHAR(40)), | employee VARCHAR(40)), | ||
title VARCHAR(40)); | title VARCHAR(40)); | ||
| − | INSERT INTO jobs VALUES ('Gordon | + | INSERT INTO jobs VALUES ('Gordon Russell','Lecturer'); |
INSERT INTO jobs VALUES ('Andrew Cumming','Teaching fellow'); | INSERT INTO jobs VALUES ('Andrew Cumming','Teaching fellow'); | ||
INSERT INTO jobs VALUES ('Jim Smith','Technician'); | INSERT INTO jobs VALUES ('Jim Smith','Technician'); | ||
Revision as of 09:10, 30 July 2012
Converting subqueries into Join functions.
schema:scott
DROP TABLE jobs, ranks, 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(20), rank VARCHAR(20)); INSERT INTO ranks VALUES ('Lecturer','LECT1'); INSERT INTO ranks VALUES ('Teaching fellow','LECT2'); INSERT INTO ranks VALUES ('Technician','TECH1'); CREATE TABLE salary ( rank VARCHAR(20), payment INTEGER); INSERT INTO salary VALUES ('LECT1',2000.00); INSERT INTO salary VALUES ('LECT2',3000.00); INSERT INTO salary VALUES ('TECH1',5000.00); INSERT INTO salary VALUES ('TECH2',6000.00 );
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 JOIN
- 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 WHERE for AND after the first occurence of WHERE
SELECT payment FROM salary, ranks, jobs WHERE salary.rank = ranks.rank AND ranks.title = jobs.title AND jobs.employee = 'Andrew Cumming'
{Hacks Ref}
