What are different types of joins in SQL, in MS SQL Server?

Answer 1

INNER JOIN
-----------------
Inner join shows matches only when they exist in both tables.Example, in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid.So this SQL will only give you result with customers who have orders.If the customer does not have order it will not display that record.

SELECT Customers.*,
                         Orders.*
    FROM Customers
INNER JOIN Orders
ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN
--------------------------
Left join will display all records in left table of the SQL statement.In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*,
                         Orders.*
   FROM Customers
     LEFT OUTER JOIN Orders
        ON Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN
-----------------------------
Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*,
                         Orders.*
    FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID =Orders.CustomerID