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 ordersThe 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.OrderDateTable 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.
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.OrderDateLearningDub.com helps you understand
LearningDub.com will put you through the drill and provided useful information about: