What are the different types of joins and what is the difference between them?

Question

Hai, friends anybody explain the different types of BACKUPs available in SQL Server 2005

Answer 1

As per SQL concern and advancement, there are 3-types of joins and all RDBMS joins can be achvied using these types of joins.

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

Answer 2

1. JOIN or INNER JOIN
2. OUTER JOIN
   2.1 LEFT OUTER JOIN or LEFT JOIN
   2.2 RIGHT OUTER JOIN or RIGHT JOIN
   2.3 FULL OUTER JOIN or FULL JOIN
3. NATURAL JOIN
4. CROSS JOIN
5. SELF JOIN
   1.INNER JOIN :
   In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported.

2.OUTER JOIN :
Outer Join retrieves
Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).
There are three kinds of Outer Join :

2.1 LEFT OUTER JOIN or LEFT JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

2.2 RIGHT OUTER JOIN or RIGHT JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

2.3 FULL OUTER JOIN or FULL JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

3. NATURAL JOIN :
It is based on the two conditions :

the JOIN is made on all the columns with the same name for equality.
Removes duplicate columns from the result.

4. CROSS JOIN :
It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).

5. SELF JOIN :
It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.