Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Inner Join Syntax
Select Column(s) from LeftTable
Inner join RightTable on LeftTable.JoinField=RightTable.JoinField
LeftTable and RightTable can be switched without changing the result set only for an inner join (equijoin or natural join)
Example:
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers Inner Join Orders ON Customers.CustomerID = Orders.CustomerID
Notes:
The above example is a natural join, because the join key CustomerID is only referenced once as Customers.CustomerID in the SELECT clause.
Keys must match for rows to be in the result set—thus, including the matching key Orders.CustomerID in the SELECT clause to create an equijoin would be redundant:
SELECT Customers.CustomerID, Customers.CompanyName, Orders.CustomerID, Orders.OrderID, Orders.OrderDate FROM Customers Inner Join Orders ON Customers.CustomerID = Orders.CustomerID
Usually the keys are primary key to foreign key, but don't have to be as long as the keys have identical datatypes.
Do row counts on tables before and after the join, to ensure that records are not lost due to non-matching keys.
To do a row count, use Count(*) in the SELECT clause for the respective tables, either in the SQL Query Analyzer or the SQL pane of the table data grid in the Enterprise Manager:
Select Count(*) from Table1
If the count is restored after changing one of the joins to a left outer join, there's a non-matching key problem. A relationship may not be in force between the tables, or may not have been in force when the table was created.
Left Outer Join Syntax
Select Column(s) from LeftTable
Left outer join RightTable on LeftTable.JoinField=RightTable.JoinField
LeftTable and RightTable cannot be switched without affecting the query result set unless the corresponding join condition is changed to right outer join
Example:
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers Left Outer Join Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.CustomerID is null
This example is used to find customers without orders.
Notes:
Keys must match or one of the keys must be a null.
Usually the keys are primary key to foreign key, but don't have to be as long as the keys have identical datatypes.
Used to find unmatched records—records in the LeftTable that don't have a match in the RightTable, or simply every record in the LeftTable whether a match exists or not.
Right Outer Join Syntax
Select Column(s) from LeftTable
Right outer join RightTable on LeftTable.JoinField=RightTable.JoinField
LeftTable and RightTable cannot be switched without affecting the query result set unless the corresponding join condition is changed to left outer join
Example:
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers Right Outer Join Orders ON Customers.CustomerID = Orders.CustomerID
Notes:
Keys must match or one of the keys must be a null.
Usually the keys are foreign key to primary key, but don't have to be as long as the keys have identical datatypes.
Used to find orphaned records. In a relational database such as SQL Server, relationships are established between tables by primary keys and foreign keys. A primary key in one table can be related to many foreign keys in another table. To maintain data integrity, there should be no foreign keys without a corresponding primary key in the first table. If this condition exists, the records are called orphaned children.
Full Outer Join Syntax
Select Column(s) from LeftTable
Full outer join RightTable on LeftTable.JoinField=RightTable.JoinField
LeftTable and RightTable can be switched
Example:
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers Full Outer Join Orders ON Customers.CustomerID = Orders.CustomerID
Notes:
Keys must match or one of the keys must be a null.
Usually the keys are foreign key to primary key, but don't have to be as long as the keys have identical datatypes.
Used to find all rows in both tables.
Cross Join Syntax
Select Column(s) from LeftTable
Cross join RightTable
LeftTable and RightTable can be switched
No ON clause because no relationship is set between the two tables
Example:
SELECT Employees.FirstName, Customers.LastName from Employees Cross Join Customers
Notes:
Useful in creating dummy or test data. A table with 25 first names and another with 25 last names would produce 625 combination names. (Could even use the same table in a self-join.)
Useful in initializations. A cross join between a student class list and a class days table would produce a student days table with one row for each student for each class day.
Every row in the first table is paired with every row in the second table to produce a result set of the number of rows in the first table times the number of rows in the second table.
Often done unintentionally by using the following syntax:
Select FirstTable.Column(s), SecondTable.Column(s) from FirstTable, SecondTable
Can produce a lot of rows: Two tables with a thousand rows each would produce a cross join of a million rows.