The INNER JOIN places the data of two tables “side by side”. The JOIN condition, which is introduced with ON , determines which rows of the left table end up in a row of the result table together with which rows of the right table. In the figure is an example of this with the following query:
SELECT i.docnr,
i.amount,
i.curr,
c.text
FROM invoice as i
INNER JOIN currency as c
ON i.curr = c.curr
Combinatorics with INNER JOIN
- Records in a table that find at least one partner in the other table using the ON condition are included in the result set. In the example: EUR, ALL and USD
- Data records without a partner do not appear in the result. In the example: DEM from the left table and GBP from the right table.
- If a record finds more than one partner, then it occurs correspondingly more than once. In the example: EUR from the right table becomes the first two records of the left table.
The INNER JOIN places the data of two tables “side by side”. The JOIN condition, which is introduced with ON , determines which rows of the left table end up in a row of the result table together with which rows of the right table. In the figure is an example of this with the following query:
In our example in the figure you can see that the INNER JOIN can also have undesired effects. Data records from the INVOICE table are not transferred because there is no entry in the CURRENCY table. This problem will be solved with the other JOIN types in the next topic.
The JOIN condition
The JOIN condition begins with the keyword ON . This must always be specified for the INNER JOIN . You can imagine a JOIN as if the cross product of the data sets, i.e. all combinations of left and right table, were formed first. And then it checks line by line if the JOIN condition is true.
The same predicates are allowed as in the WHERE clause. In particular, it is also possible to filter on the data before entering the JOIN.
Equi-JOINs
The fastest way to execute JOIN conditions is to check for equality and, if there are multiple columns in a join, to use AND. These are called Equi-JOINs.