6
6
Queries can access multiple tables at once, or access the same table in such a way that multiple rows of the table are being processed at the same time. For instance, in order to list all the 'interest' records together with the location of the associated city. To do this, it's necessary to compare the city column of each row of the 'Interests" table with the name column of all rows in the cities table, and select the pairs of rows where these values match. As such, a JOIN statement involves combining records from two tables by using values common to each.
8
8
Take the "Interests" table:
10
+---------+--------------+--------------+-------------+----------+
11
|Id |DateAdded |InterestType |Name | userID |
12
+=========+==============+==============+=============+==========+
13
| 1 |2011-01-02 | Sport |45 |2 |
14
+---------+--------------+--------------+-------------+----------+
15
| 2 |2011-01-02 | Art |10 |4 |
16
+---------+--------------+--------------+-------------+----------+
17
| 3 |2011-01-02 | Music |25 |1 |
18
+---------+--------------+--------------+-------------+----------+
19
| 4 |2011-01-02 | Food |125 |1 |
20
+---------+--------------+--------------+-------------+----------+
21
| 5 |2011-01-03 | Music |40 |2 |
22
+---------+--------------+--------------+-------------+----------+
23
| 6 |2011-01-03 | Food |20 |3 |
10
+---------+--------------+--------------+-------------+----------+
11
|Id |DateAdded |InterestType |Name | userID |
12
+=========+==============+==============+=============+==========+
13
| 1 |2011-01-02 | Sport |45 |2 |
14
+---------+--------------+--------------+-------------+----------+
15
| 2 |2011-01-02 | Art |10 |4 |
16
+---------+--------------+--------------+-------------+----------+
17
| 3 |2011-01-02 | Music |25 |1 |
18
+---------+--------------+--------------+-------------+----------+
19
| 4 |2011-01-02 | Food |125 |1 |
20
+---------+--------------+--------------+-------------+----------+
21
| 5 |2011-01-03 | Music |40 |2 |
22
+---------+--------------+--------------+-------------+----------+
23
| 6 |2011-01-03 | Food |20 |3 |
24
24
+---------+--------------+--------------+-------------+----------+
50
50
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
51
51
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
52
52
* FULL JOIN: Return rows when there is a match in one of the tables
55
FIXME: how joins are executed. i.e. nested loop join.
added
removed
docs/join.rst
