.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
satyapriyanayak
Jean Paul
SP
abhays

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Combine different tables in SQL

Posted By:Abhisek Panda       Posted Date: November 29, 2009    Points: 25    Category: DataBase    URL:   
 

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.
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend