The SELECT statement syntax given at the end of the previous chapter shows that more than one table may be pointed in the FROM clause. A table listing that does not use WHERE clause is practically unused because this produces the relational operation of the Cartesian product of the tables involved. That is, each record in one table meshes with each record in another table. For example, the tables
A
a
b
1
2
2
1
B
c
d
2
4
3
3
in the query
SELECT *
FROM A, B;
produce the following result:
a
b
c
d
1
2
2
4
1
2
3
3
2
1
2
4
2
1
3
3
As a rule, the table listing is used with the condition of joining records from different tables in the WHERE clause. The condition for the above tables may be a coincidence of the values, say, in the columns a and c:
SELECT *
FROM A, B
WHERE a = c;
Now the result set of that query is the following table:
a
b
c
d
2
1
2
4
i.e. only those table rows that have equal values in specified columns (equi-join) are joined. Although some arbitrary conditions may be used, nevertheless the equi-join is most commonly used because this operation reproduces a certain entity split by two entities as a result of a normalization process.
Even though different tables have columns with the same names, the dot notation is needed for uniqueness:
<table_name>.<column_name>
In cases where ambiguity is not available, that notation need not be used.
go
(5.6.1.)