SQL Joins

only for RuBoard

There are many types of Join statements that allow you to retrieve data from two or more tables based on logical relationships between those tables. For instance, let's say you have a Students and Classes table in a database used by a local college, and each of the tables has a field named StudentID. By using a join statement, you can converge the two tables in a SQL query into one result set. For the most part, a Join condition needs two things:

• To specify a column from each table to be joined; typically it's a primary key/foreign key relationship

• A logical operator to be used when comparing values from the specified columns (such as Column A = Column B)

Take a look at the following example illustrating how to use a Join . This example joins the Products and Suppliers table based on the SupplierID column value :

 1: SELECT * 2: FROM Suppliers AS S 3: INNER JOIN Products AS P 4: ON 5: S.SupplierID = P.SupplierID 6: WHERE 7: S.SupplierID = '1' 

When the previous code example executes, 4 rows will be returned in the result set, one for each product in the Products table. Each row in the result set will contain every column from both the Suppliers and Products table. There are three types of joins; the following list contains the name and description of each:

• INNER JOIN The most commonly used JOIN statement. It uses a comparison operator, such as the equals sign (=), to match rows on tables based on the specified columns of each table.

• OUTER JOIN There are multiple types of OUTER JOINS ; included is the LEFT OUTER JOIN , RIGHT OUTER JOIN , and FULL JOIN . An OUTER JOIN returns all the rows in the joined table, either the LEFT or RIGHT . In the case of the FULL JOIN, all the rows from both tables are returned.

• CROSS JOIN Returns all rows from the left table. Each row from the left table is combined with all rows from the right table.

only for RuBoard