The equijoin is the most commonly used kind of table join. Equijoins include a join condition requiring the values derived from one column of a table or view to equal the values derived from another column of a different table or view. Join conditions may be created on any combination of columns, provided the qualified column names being compared in the WHERE clause have compatible data types.
For example, suppose the supplier numbers and city names for all suppliers that supply part "P2" are needed. The next query illustrates how this could be accomplished:
SELECT sno, city
FROM partsupp, supplier
WHERE partsupp.sno = supplier.sno
AND pno = "P2"
The query results appear below:
| sno | city |
|---|---|
| S2 | PARIS |
| S3 | PARIS |
| S1 | LONDON |
| S4 | LONDON |
As demonstrated in the previous query, when the tables to be joined have a common column name appearing in the WHERE clause join condition, these column names should be prefixed (qualified) with their table names to resolve any ambiguity. In the above query, the qualified column name PARTSUPP.SNO specifies that the SNO column of the PARTSUPP table is to be compared with the SNO column of the SUPPLIER table (SUPPLIER.SNO).
If you do not qualify a column name that is contained in more than one query statement table, the system uses column data from the first table it finds containing the specified column name. This situation can produce unexpected results, and it is advisable to prefix common column names their specific "owning" table names.
If a join condition (such as, partsupp.sno = supplier.sno) is not included in a equijoin query, the result is a Cartesian product. This type of join concatenates every record from a table with every record from the other tables listed in the FROM clause, producing all this information in the query result. Cartesian products have limited practical value and can result in extremely large and often nonsensical results.