Join Multiple Tables In SQL

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

CROSS JOIN

SELECT*FROM table1 CROSS JOIN table2

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.