loading..
Русский    English
13:58

Using multiple tables in a query page 1

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

  1. SELECT *
  2. 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:

  1. SELECT *
  2. FROM A, B
  3. 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.

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.