Relational databases would not be that useful if query statements were limited to a single table. Multiple tables can be queried together using JOIN statements. They are essentially nested queries that span across one or more tables using a like piece of information.
¶ Nested Queries (Subqueries)
The simplest place to nest a query is inside the condition of another statement:
SELECT * FROM <table1> WHERE <column1> = (
SELECT <column1> FROM <table1> WHERE <column1> = 'VALUE'
);
This does exactly the same thing as:
SELECT * FROM <table1> WHERE <colum1> = 'VALUE';
The “nested” query always runs first. The result of the nested query then becomes the input for the surrounding query. You can also embed a subquery inside the FROM clause:
SELECT * FROM ( SELECT * FROM <table1> );
The nested query essentially becomes the table of data to pull from.
When you embed a query in the SELECT clause, the subquery acts as a value to append as an additional column. If you use a subquery that returns more than a single value, it will throw an error.
¶ Example
SELECT species, type, ( SELECT name FROM ex_pets WHERE id = 1 ) AS Oliver FROM ex_pets;
| species | type | Oliver |
|---|---|---|
| dog | dachshund | Oliver |
| cat | british_shorthair | Oliver |
| cat | birman | Oliver |
| dog | poodle | Oliver |
| guinea_pig | american | Oliver |
¶ INNER JOIN
If there is a relationship between two tables, meaning that one column has the same value in both tables, you can use a JOIN clause to query information in one table from another. INNER JOIN simply means “join on common values”. The old style of INNER JOIN is one way to join two tables together. This style uses the period operator (.), which will find a column within a table, so long as it is specified in the FROM clause.
SELECT * FROM <table1>, <table2> WHERE <table1>.<column1> = <table2>.<column1>;
You can also define aliases for table names, which can then be used when comparing data from different tables.
SELECT * FROM <table1> <alias1>, <table2> <alias2>
WHERE <alias1>.<column1> = <alias2>.<column2>;
If you decide to use aliases, you can also reference them in the table names, but once they are declared, you cannot use their original column names.
¶ Example
SELECT p.name, p.species, o.date_adopted FROM ex_pets p, ex_owners o WHERE p.id = o.pet_id;
| name | species | date_adopted |
|---|---|---|
| Oliver | dog | 8/16/19 |
| Silverbell | cat | 5/7/19 |
| Annie | dog | 9/16/18 |
| Susie | guniea_pig | 6/24/18 |
¶ Using the Keywords
Using the INNER JOIN ON keyword pair is far more explicit and is accepted as the standard for using inner join’s.
SELECT * FROM <table1> INNER JOIN <table2> ON <table1>.<column1> = <table2>.<column1>;
¶ OUTER JOIN
¶ LEFT JOIN
Picture two tables side-by-side; The first declared table being on the left, and the second declared table on the right. The LEFT JOIN will essentially match All records on the left and the Matching records on the right.
SELECT * FROM <table1> LEFT JOIN <table2> ON <table1>.<column1> = <table2>.<column1>;
¶ RIGHT JOIN
The RIGHT JOIN works exactly the same as the LEFT JOIN, but will match All records on the right and the Matching records on the left.
SELECT * FROM <table1> LEFT JOIN <table2> ON <table1>.<column1> = <table2>.<column1>;
¶ FULL OUTER JOIN
Contrary to querying all of the information on the left or the right table and the matching information on the other, the FULL JOIN with query All of the information from Both tables.
SELECT * FROM <table1> FULL OUTER JOIN <table2>
ON <table1>.<column1> = <table2>.<column1>;
¶ EXISTS
The EXISTS condition is used in combination with a WHERE clause to test if there is a result from a subquery (At least one query). The output if the condition is true is all of the data queried in the SELECT statement.
SELECT * FROM <table1> WHERE EXISTS (<subquery>);
¶ NOT EXISTS
THE NOT EXISTS condition negates the EXISTS condition and returns all of the data queried in the SELECT statement if the condition is false.
SELECT * FROM <table1> WHERE NOT EXISTS (<subquery>);
¶ UNION
The UNION statement is simply used to combine the results of two queries. Simply separate the two queries with the UNION keyword.
SELECT * FROM <table1> UNION SELECT * FROM <table2>;
¶ UNION ALL
This works the same as UNION, but it will not remove duplicate columns.
¶ INTERSECT
INTERSECT combines both sets of rows so that only those rows that were present in both SELECT statements appear in the final result.
SELECT * FROM <table1> INTERSECT SELECT * FROM <table2>;
¶ MINUS
MINUS starts with the first set of rows and then uses the second SELECT statement's row set to see whether any duplicates occur. If they do, those duplicates are completely removed, leaving only those rows that uniquely exist in the first SELECT statement's row set. Simply put, this is the first query MINUS identical rows in both queries.
SELECT * FROM <table1> MINUS SELECT * FROM <table2>;
¶ NATURAL JOIN
A natural join is an inner join by default, but it can also be a left outer join, right outer join, or a full outer join.
SELECT <column1>, <column2> FROM <table1> NATURAL JOIN <table2>;
As long as their exists a like-named column on both tables, NATURAL JOIN will automatically join on that column.
It can also work with OUTER JOIN as well.
SELECT <column1>, <column2> FROM <table1> NATURAL RIGHT OUTER JOIN <table2>;
SELECT <column1>, <column2> FROM <table1> NATURAL FULL OUTER JOIN <table2>;
¶ USING
The keyword USING is similar to the natural join, in the sense that its use depends on the presence of identically named columns in the JOIN. Like NATURAL, USING can be used with both inner and outer joins.
SELECT <column1>, <column2> FROM <table1> LEFT JOIN <table2>;