The join predicate in the SQL statement is what contains the rules or conditions that identify what records are to be joined. When the SQL is evaluated, the predicate conditions are tested. If the predicate conditions are true then the combined record is produced.
An INNER JOIN is the most common join operation and is generally the default join type. An inner join creates a result by combining column values of two tables (A and B) based upon the join predicate. The join query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join predicate.
The INNER JOIN example below will only return a result when there is at least one valid match in both tables. If there are rows in "Users" that do not have matches in "Sales", those rows will not be in the result set (not returned).
Inner Join Example:
SELECT Users.Last_Name, Users.First_Name, Sales.Order_Number
An OUTER JOIN does not require each record in the two joined tables to have a matching record. The joined table keeps each record even if no other matching record exists. Outer joins can be classified further into "left outer joins", "right outer joins", and "full outer joins", depending on which tables the rows are retained from (left, right, or both).
The OUTER JOIN clause is a variation of the SQL JOIN clause which enables a SELECT statement to process more than one table. Use the OUTER JOIN to return results from multiple tables when there aren't any matches between the JOINed tables. The example OUTER JOIN below will select from the Users and Orders tables all of the distinct users (first and last names) and the total amount they've spent:
SELECT Users.FirstName, Users.LastName, SUM(Orders.OrdersAmount) AS OrdersPerUser
NOTE: A "cartesian product" will result if there are no matching join columns specified. This means that a row will be returned for EVERY combination of the two tables. If one table has 1,000 rows and the second table has 3,000 rows the result set will contain 3,000,000 (three million!) rows. This is probably NOT what you want.
A SELF JOIN is a query in which a table is joined (compared) to itself. Self-joins are useful to compare values in a specific column with other values in the same column in the same table (related data that is stored in a single table). The self join can be done by using table aliases to treat one table as though it were a different table and then joining them together. Another common use for self-joins is obtaining running counts and totals in a query.
The general rule for writing self-joins is:
SELECT DISTINCT c1.UserName, c1.Address, c1.City, c1.ZipCode
Related Code Snippets: