Skip to main content
MSSQL - Tip, Tricks & Shortcuts

Querying Multiple Tables using Joins

Joins are used to retrieve data from two or more tables or views based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.

A joined table is a result set that is the product of two or more tables or views. For multiple joins, parentheses are used to change the natural order of the joins.

The basic syntax for a JOIN is as follows:

SELECT table1.column, table2.column
FROM table1 JOIN table2
ON (table1.column=table2.column)
WHERE conditions

Creating a simple report that returns the CustomerID and SalesOrderID from the SalesOrderHeader table is not difficult.

-- Find the CustomerID and SalesOrderID for all orders
SELECT CustomerID, SalesOrderID
FROM SalesLT.SalesOrderHeader;

The first thing you will notice is that the report you created above is not very useful because we cannot tell who the customer is that got this order. This is where a JOIN comes to the rescue. The report below shows how we can use a join to make the report more useful.

-- Create a report showing customer orders. SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName, SalesOrderHeader.SalesOrderID, SalesOrderHeader.OrderDate
FROM SalesLT.Customer JOIN SalesLT.SalesOrderHeader ON
(Customer.CustomerID = SalesOrderHeader.CustomerID)
ORDER BY SalesOrderHeader.OrderDate;

Table names are used as prefixes of the column names to identify the table in which to find the column. e.g. Customer.CustomerID, SalesOrderHeader.OrderDate, etc. Although this is only required when the column name exists in both tables, it is always a good idea to include the prefixes as it makes your code more efficient and easier to read.

Table Aliases

Using full table names as prefixes can make SQL queries unnecessarily wordy as you can see in the last example. Table aliases can make the code a little more concise. The following example, which is identical in functionality to the query above, illustrates the use of table aliases:

-- Create a report showing customer orders using Aliases. SELECT c.CustomerID, c.FirstName, c.LastName,s.SalesOrderID, s.OrderDate
FROM SalesLT.Customer c JOIN SalesLT.SalesOrderHeader s ON
(c.CustomerID = s.CustomerID)
ORDER BY s.OrderDate; Joined table Join type Specifies the type of join operation. ////////////////////////////// Outer joins, returns all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. //////////////////// Inner join Left outer join Right outer join Full outer join Cross join
Using INNER JOIN or JOIN

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins returns no records when the rows do not match with a row from the other table.

Using LEFT OUTER JOIN or LEFT JOIN

LEFT JOIN returns all the records from left table and only the matched records from right table. The records from left table which are not matched from the right table are set to NULL.

Using RIGHT OUTER JOIN

RIGHT JOIN: returns all the records from right table and only the matched records from left table.

Using FULL OUTER JOIN

The full outer join or full join returns all rows from both left and right tables, with the matching rows from both sides if available. In case there is no match, the columns of the table will be filled with NULL.

Using CROSS JOIN
Using Cross Join

Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
Using SELF JOIN
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

How BXImpact Credit Repair can help

LearningDub.com helps you understand

LearningDub.com will put you through the drill and provided useful information about:





Scroll to Top