How to Use an Equi-join in SQL
The most common join that uses the SQL WHERE clause filter is the equi-join. An equi-join is a basic join with a WHERE clause that contains a condition specifying that the value in one column in the first table must be equal to the value of a corresponding column in the second table. Applying an equi-join to the example tables brings a more meaningful result:
SELECT * FROM EMPLOYEE, COMPENSATION WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ;
This query produces the following results:
EmpID FName LName City Phone Employ Salary Bonus ----- ------ ----- ---- ----- ------ ------ ----- 1 Whitey Ford Orange 555-1001 1 33000 10000 2 Don Larson Newark 555-3221 2 18000 2000 3 Sal Maglie Nutley 555-6905 3 24000 5000 4 Bob Turley Passaic 555-8908 4 22000 7000
In this result table, the salaries and bonuses on the right apply to the employees named on the left. The table still has some redundancy because the EmpID column duplicates the Employ column. You can fix this problem by slightly reformulating the query, like this:
SELECT EMPLOYEE.*,COMPENSATION.Salary,COMPENSATION.Bonus FROM EMPLOYEE, COMPENSATION WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ;
This query produces the following result table:
EmpID FName LName City Phone Salary Bonus ----- ----- ----- ---- ----- ------ ----- 1 Whitey Ford Orange 555-1001 33000 10000 2 Don Larson Newark 555-3221 18000 2000 3 Sal Maglie Nutley 555-6905 24000 5000 4 Bob Turley Passaic 555-8908 22000 7000
This table tells you what you want to know but doesn’t burden you with any extraneous data. The query is somewhat tedious to write, however. To avoid ambiguity, you can qualify the column names with the names of the tables they came from. Typing those table names repeatedly provides good exercise for the fingers but has no other merit.
You can cut down on the amount of typing by using aliases (or correlation names). An alias is a short name that stands for a table name. If you use aliases in recasting the preceding query, it comes out like this:
SELECT E.*, C.Salary, C.Bonus FROM EMPLOYEE E, COMPENSATION C WHERE E.EmpID = C.Employ ;
In this example, E is the alias for EMPLOYEE, and C is the alias for COMPENSATION. The alias is local to the statement it’s in. After you declare an alias (in the FROM clause), you must use it throughout the statement. You can’t use both the alias and the long form of the table name in the same statement.
Even if you could mix the long form of table names with aliases, you wouldn’t want to, because doing so creates major confusion. Consider the following example:
SELECT T1.C, T2.C FROM T1 T2, T2 T1 WHERE T1.C > T2.C ;
In this example, the alias for T1 is T2, and the alias for T2 is T1. Admittedly, this isn’t a smart selection of aliases, but it isn’t forbidden by the rules. If you mix aliases with long-form table names, you can’t tell which table is which.
The preceding example with aliases is equivalent to the following SELECT statement with no aliases:
SELECT T2.C, T1.C FROM T1 , T2 WHERE T2.C > T1.C ;
SQL enables you to join more than two tables. The maximum number varies from one implementation to another. The syntax is analogous to the two-table case; here’s what it looks like:
SELECT E.*, C.Salary, C.Bonus, Y.TotalSales FROM EMPLOYEE E, COMPENSATION C, YTD_SALES Y WHERE E.EmpID = C.Employ AND C.Employ = Y.EmpNo ;
This statement performs an equi-join on three tables, pulling data from corresponding rows of each one to produce a result table that shows the salespeople’s names, the amount of sales they are responsible for, and their compensation. The sales manager can quickly see whether compensation is in line with production.
Storing a salesperson’s year-to-date sales in a separate YTD_SALES table ensures better computer performance and data reliability than keeping that data in the EMPLOYEE table. The data in the EMPLOYEE table is relatively static. A person’s name, address, and telephone number don’t change very often. In contrast, the year-to-date sales change frequently (you hope).
Because the YTD_SALES table has fewer columns than the EMPLOYEE table, you may be able to update it more quickly. If, in the course of updating sales totals, you don’t touch the EMPLOYEE table, you decrease the risk of accidentally modifying employee information that should stay the same.