SQL Server - inner join and outer join

Hai,

     What is the inner join and outer join? How it is used and What is the purpose of this?



Thanks & Regards
Suresh.K

An Inner Join compares two tables based on some values and rows that satisfy the join predicate are returned. It is like an Intersection.

SELECT Col1, Col2 
FROM   Table1 INNER JOIN Table2
ON   Table1.PrimaryKey = Table2.PrimaryKey

An Outer Join also joins two tables, but does not require  each record to in the two tables to be matching.

For more, read from tons of other posts on the internet. http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ is one such one.

hi

Inner Join:
 It is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.

Outer Join:

 This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

In outer join we have three types:

Left Outer
Right Outer
Full Outer

refer links for examples

http://www.techonthenet.com/sql/joins.php
http://database.ittoolbox.com/documents/inner-and-outer-join-sql-statements-18442

In simple word both join works on two table based on some matching condition only the differance is if we are using INNER Join it return those row matching condition on both table where as OUTER Join return all rows from one table and matching row form other tables

eg
Select t1.*, t2.*  From tblname1 t1 INNER JOIN tblnmae2 ON t1.id = t2.id
Above query use inner join so return those record havind id in both table 

Select t1.*, t2.*  From tblname1 t1 Left OUTER JOIN tblnmae2 ON t1.id = t2.id 
in above query return all record from left table  and those record habing same id in 2nd table

Reference from-

http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

• INNER JOIN - Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data.  Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.
  • Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and/or queries.  Although that is the case in some environments, it is really dependent on the database design, referential integrity and data needed for the application.  As such, please take the time to understand the data being requested then select the proper join option.
  • Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.
• LEFT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the left table.  On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table.
  • Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite of one another.  So you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.
• RIGHT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the right table.  On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.
• Self -Join - In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.
• CROSS JOIN - Based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows.  The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table.  Please heed caution when using a CROSS JOIN.
• FULL JOIN - Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

Hope this will help you.

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

SQL Inner Join:

All the rows returned by the sql query satisfy the sql join condition specified.

For example: If you want to display the product information for each order the query will be as given below. Since you are retrieving the data from two tables, you need to identify the common column between these two tables, which is theproduct_id.

The query for this type of sql joins would be like,

SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;

The columns must be referenced by the table name in the join condition, because product_id is a column in both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT statement.

The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number of tables involved. The rule must be true to avoid Cartesian product.

We can also use aliases to reference the column name, then the above query would be like,

SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units
FROM product p, order_items o
WHERE o.product_id = p.product_id;

SQL Outer Join:

This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only.

The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join".

If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below:

SELECT p.product_id, p.product_name, o.order_id, o.total_units
FROM order_items o, product p
WHERE o.product_id (+) = p.product_id;