Subquery and JOIN
Here you are shown how to convert subqueries into JOIN functions this would be done as using subqueries that contain no aggregate functions unnecessarily causes the response speed to slow down.
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 single
- Delete all occurrences of (
WHEREfor AND after the first occurence of
SELECT payment FROM salary, ranks, jobs WHERE salary.rank = ranks.rank AND ranks.title = jobs.title AND jobs.employee = 'Andrew Cumming'
JOIN ONfunctions would also work with
SELECT payment FROM salary JOIN ranks ON (salary.rank = ranks.rank) JOIN jobs ON (ranks.title = jobs.title)WHERE jobs.employee = 'Andrew Cumming'
Hack 10 Converting subqueries into joins