Join Multiple Tables In SQL

Abhisek
Posted by in Sql Server category on for Beginner level | Views : 7563 red flag

This article describes about how you can join different tables in SQL. It describes different join operations.
Combining Tables


         A query result can include data from multiple tables. To combine data from multiple tables, you can use the JOIN operation from SQL. The JOIN operation matches the rows of one table with rows of another table, based on values in those rows.


Types of JOIN



1. Inner Join
2. Outer Join
        Left-Outer Join
        Right-Outer Join
        Full-Outer Join
3. Cross Join

INNER JOIN
SELECT table1.col, table1.col, table2.col, table2.col FROM table1 INNER JOIN table2 ON table1.col = table2.col

    
      It is a join that displays only the rows that have a match in both the joined tables.

      This is the default type of join in Query Designer.

      Columns containing NULL do not match any values when you are creating an inner join and are excluded from the result set. NULL values do not match other NULL values.

LEFT OUTER JOIN

SELECT table1.col, table1.col, table2.col, table2.col FROM table1 LEFT OUTER JOIN table2 ON table1.col = table2.col


         It is a join  that includes rows even if they do not have related rows in the joined table.

         All rows from the first named table(the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear.

         For every unmatched record a NULL value will appear for the rightmost table.

RIGHT OUTER JOIN
SELECT table1.col, table1.col, table2.col, table2.col FROM table1 RIGHT OUTER JOIN table2 ON table1.col = table2.col


          It is a join that includes rows even if they do not have the related rows in the joined table.

          All the rows from the second-named table(the "right" table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table do not appear.

         For every unmatched record a NULL value will appear in the leftmost table.

FULL OUTER JOIN
SELECT table1.col, table1.col, table2.col, table2.col FROM table1 FULL OUTER JOIN table2 ON table1.col = table2.col

          It is a join that includes rows even if they do not have related rows in the joined table.

          All rows in all joined tables are included, whether they are matched or not.

         For every unmatched record a NULL value will appear for the corresponding table.

CROSS JOIN
SELECT*FROM table1 CROSS JOIN table2

It is a join whose result set includes one row for each possible pairing of rows from the two tables.


Page copy protected against web site content infringement by Copyscape

About the Author

Abhisek
Full Name: Abhisek Panda
Member Level: Bronze
Member Status: Member
Member Since: 10/11/2009 6:25:59 AM
Country: India
Abhisek Panda

Thanks and Regards Abhisek Panda Go Green And Save Your Future

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)