SQL Server - Joins

Join statements are used to connect additional data to your select statement.

For example

select * from OrderHeader

gets all data from the OrderHeader table

Select *
from OrderHeader o
Join Vendor as v on o.vendorid=v.vendorid

This statement gathers all order header records, and for each order record, connect the vendor table. Get the Vendor record whose VendorID matches the VendorID contained in the Order Header table.

Actually there are a lot of types of joins. For example

Select *
from OrderHeader o
Left Join Vendor as v on o.vendorid=v.vendorid

Each of the types of joins will be described below.

Join

For example

Select *
from OrderHeader o
Join Vendor as v on o.vendorid=v.vendorid

This statement gathers all order header records, and for each order record, connect the vendor table. Get the Vendor record whose VendorID matches the VendorID contained in the Order Header table. The output will be a list of records containing all the columns in both the OrderHeader table and the Vendor table.

Note: If the vendor table does not have that vendor, then that record will not be included in the output.

Left Join

For example

Select *
from OrderHeader o
Left Join Vendor as v on o.vendorid=v.vendorid

This statement gathers all order header records, and for each order record, connect the vendor table. Get the Vendor record whose VendorID matches the VendorID contained in the Order Header table. The output will be a list of records containing all the columns in both the OrderHeader table and the Vendor table.

Note: If the vendor table does not have that vendor, then the OrderRecord record will still be included in the output, but the columns associated with the vendor will be null.

Example

Give me a list of OrderHeader records that have a problem joining to the Vendor table

Select *
from OrderHeader o
Left Join Vendor as v on o.vendorid=v.vendorid
where v.vendorId is null