Browse DevX
Sign up for e-mail newsletters from DevX


Getting the Right Data with SQL Joins : Page 6

Associating tables in SQL is usually a breeze. But what happens when you find yourself looking at the completely wrong set of records and you don't know why? Find out how correctly applying joins can help you understand and avoid these mysterious data returns.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The Cross
A cross join returns what's known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. As you can imagine, sometimes this join produces a mess, but under the right circumstances, it can be very useful. For instance, suppose you want to combine each product with a pricing table so you can analyze each product at each price. In one table, you have the current price of each product. In a second table, you have a list of increase values such as .05 percent, .06 percent, .10 percent, and so on. In this case, a cross join combines each product price with each increase value. The following statement creates such a matrix between the Products table and a temporary table named Amount:

SELECT Products.ProductName, Products.UnitPrice, Temporary.Amount FROM Products CROSS JOIN Temporary ORDER BY Products.ProductName, Temporary.Amount

Figure 8: Combining all the rows can produce a matrix affect known as a Cartesian product.

The above syntax works in SQL Server and Oracle 9i.

The resulting recordset, shown in Figure 8, creates a record for each product with each increase amount. The results are known as a Cartesian product, which combines every record in the left table with every record in the right table.

Jet and Oracle versions prior to 9i don't support an explicit cross join, but you can get the same results using the following statement:

SELECT Products.ProductName, Products.UnitPrice, Temporary.Amount FROM Products, Temporary ORDER BY Products.ProductName, Temporary.Amount

In truth, you are joining two records where no relationship is in use.

A Note About Self Joins
There's a special type of relationship that's called a self join. It's not really a type of join, since you can apply it to any join type. A self join is rather unique in that it involves a relationship with only one table. Instead of relating a table to a second table, the join is based on a relationship based on the same table. You simply reference the table twice and use an alias to avoid confusion. The common example used here is when you have a hierarchal reporting structure whereby one member of staff reports to another. In this case the employees in the employee table are related to one another in order to find out who manages whom. For example, this returns all records including those employees without a manager:

SELECT Employees.LastName, Employees.FirstName, Supervisors.LastName, Supervisors.FirstName FROM Employees LEFT JOIN Employees AS Supervisors ON Employees.EmployeeID = Supervisors.ReportsTo

Here, Supervisors is an alias to the Employees table. The result lists each employee and the employees that report to each employee listed in the Employees table.

Oracle 9i syntax resembles the following:

SELECT Employees.LastName, Employees, FirstName, Supervisors.LastName, Supervisors.FirstName FROM Employees LEFT OUTER JOIN Employees Supervisors ON (Employees.EmployeeID = Supervisors.ReportsTo)

If you are using an earlier version, use syntax similar to the following:

SELECT Employees.LastName, Employees.FirstName, Supervisors.LastName, Supervisors.FirstName FROM Employees, Employees Supervisors WHERE Employees.EmployeeID = Supervisors.ReportsTo(+)

Not Every System Is Alike
It would be impossible to review how each relational database system uses each of the SQL joins. This article has provided the syntax for Jet SQL, Transact-SQL, and Oracle SQL. Table A identifies which systems support each of the joins discussed.

Table A: System Supported Joins




Oracle 9i SQL

Oracle 8i



Susan Sales Harkins is an independent consultant and the author of several articles and books on database and Web technologies. Her most recent books are: SQL: Access to SQL Server, by Apress, Mastering Dreamweaver MX Databases, by Sybex, and Absolute Beginner's Guide to Microsoft Access 2002, by Que. You can reach Susan at sharkins@bellsouth.net.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date