Difference between revisions of "Subquery and JOIN"
From SQLZOO
| Line 1: | Line 1: | ||
Converting subqueries into Join functions. | Converting subqueries into Join functions. | ||
<div class='ht'> | <div class='ht'> | ||
| − | <div class=params>schema: | + | <div class=params>schema:scott</div> |
| − | <source lang=sql class='tidy'>DROP TABLE jobs</source> | + | <source lang=sql class='tidy'>DROP TABLE jobs; |
| + | DROP TABLE ranks; | ||
| + | DROP TABLE salary;</source> | ||
<source lang=sql class='setup'> CREATE TABLE jobs( | <source lang=sql class='setup'> CREATE TABLE jobs( | ||
employee VARCHAR(40)), | employee VARCHAR(40)), | ||
| Line 10: | Line 12: | ||
INSERT INTO jobs VALUES ('Jim Smith','Technician'); | INSERT INTO jobs VALUES ('Jim Smith','Technician'); | ||
CREATE TABLE ranks ( | CREATE TABLE ranks ( | ||
| − | title VARCHAR( | + | title VARCHAR(40), |
| − | rank VARCHAR( | + | rank VARCHAR(40)); |
INSERT INTO ranks VALUES ('Lecturer','LECT1'); | INSERT INTO ranks VALUES ('Lecturer','LECT1'); | ||
INSERT INTO ranks VALUES ('Teaching fellow','LECT2'); | INSERT INTO ranks VALUES ('Teaching fellow','LECT2'); | ||
INSERT INTO ranks VALUES ('Technician','TECH1'); | INSERT INTO ranks VALUES ('Technician','TECH1'); | ||
CREATE TABLE salary ( | CREATE TABLE salary ( | ||
| − | rank VARCHAR( | + | rank VARCHAR(40), |
payment INTEGER); | payment INTEGER); | ||
| − | INSERT INTO salary VALUES ('LECT1',2000 | + | INSERT INTO salary VALUES ('LECT1',2000); |
| − | INSERT INTO salary VALUES ('LECT2',3000 | + | INSERT INTO salary VALUES ('LECT2',3000); |
| − | INSERT INTO salary VALUES ('TECH1',5000 | + | INSERT INTO salary VALUES ('TECH1',5000); |
| − | INSERT INTO salary VALUES ('TECH2',6000 | + | INSERT INTO salary VALUES ('TECH2',6000); |
</source> | </source> | ||
<div> | <div> | ||
Revision as of 09:17, 30 July 2012
Converting subqueries into Join functions.
schema:scott
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 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}
