Up to this point, you have focused primarily on extracting data from a single table. Depending on how advanced your database becomes, at times you might want to extract data from multiple tables at once. If that is the case, you will need to use joins. Although there are several types of joins, two types will be covered here:
Inner JoinsOf the different types of joins, inner joins are by far the most popular. Inner joins enable you to see all the records of two tables that have a relationship established. Remember that the Employees and the CreditCards tables have a one-to-many relationship established. The two tables in the Dorknozzle database resemble the following:
Assume that you wanted to extract the information from the Employees table for EmployeeID 2. If not for inner joins, you would have to use two SELECT statements as follows: SELECT * FROM Employees WHERE EmployeeID = 2 and SELECT * FROM CreditCards WHERE EmployeeID = 2 You can begin to see how tedious this could get, not to mention that it is completely inefficient. To solve this problem, an INNER JOIN could be used as follows: SELECT Employees.EmployeeID, Employees.Name, CreditCards.Type, CreditCards.Number FROM Employees INNER JOIN CreditCards ON Employees.EmployeeID = CreditCards.EmployeeID The join effectively produces one virtual table with the following results:
Notice that the preceding table now becomes more efficient and manageable. Also notice that, rather than referencing the names of the tables, you used the TableName.Field notation. This is crucial when using joins; otherwise, you would end up with two EmployeeIDs without a direct reference to its corresponding table. NOTE Note the use of the ON operator in the preceeding SQL INNER JOIN statement. The ON operator instructs the SQL statement to join two tables on a specific primary and foreign key pairing. Outer JoinsOuter joins enable rows to be returned from a join in which one of the tables does not contain matching rows for the other table. Suppose that you have two tables that contain the following information:
Consider the following INNER JOIN statement, issued on the preceding tables: SELECT Employees.Name, Employees.AddressID, Address.AddressID, Address.Address FROM Employees INNER JOIN Address ON Employees.AddressID = Address.AddressID It returns the following results:
Notice that the record that did not contain an AddressID was excluded. Now consider the following OUTER JOIN statement: SELECT Employees.Name, Employees.AddressID, Address.AddressID, Address.Address FROM Employees OUTER JOIN Address ON Employees.AddressID = Address.AddressID The results of this statement are slightly different:
As you can see, in the case of the OUTER JOIN, all data is returned, even if no address is present for Cammy. |