SQL INNER Vs OUTER JOIN Clause

Tables in a relational database are linked together via foreign keys. To extract data from linked tables, we utilised the JOIN clause.

The join condition specifies how each table's columns are compared.

INNER JOIN and OUTSIDE JOIN are the two types of JOIN clauses in SQL.

The OUTSIDE JOIN is broken down into sections once more.

1. LEFT OUTER JOIN - This join returns all data from the left table as well as records from both tables that are matched.
2. RIGHT OUTER JOIN - returns all data from the right table as well as records from both tables that are matched.

SQL INNER JOIN Clause

Only the information from the two connected tables that is related will be kept in the INNER JOIN.

An inner join is a query that returns joined tuples from two or more tables where at least one attribute is shared. It will return nil if there are no attributes in common across tables.

Example: Let's start by using JOIN.

You'd SELECT the column name from the pets table (and rename it pet name) in this example.

Then you'd go into the owners table and rename the name column owner. This is what it would look like: SELECT pet name from pets.name and owner.name from owners.name.

You'd use FROM to indicate that the columns come from the pets table, and JOIN to indicate that you want to join it with the owners table. JOIN the owner from the pets.

Finally, when the owner id field in the pets table equals the id column in the owner table, you would use ON pets.owner id = owners.id to combine two rows together.

SELECT CO_Order.CID
FROM CO_Order
INNER JOIN CO_Cstomer
ON CO_Customer.CID=CO_Order.CID;

SQL OUTER JOIN Clause

In SQL, it's a form of JOIN operation. Even if the join condition fails, an outer join returns the merged tuples from a given table. In SQL, there are three types of outer joins:

Join the left outer ring, the right outer ring, and the full outer ring.

LEFT JOIN is the most widely used join type in all SQL versions. However, this is not the case for the RIGHT JOIN and FULL JOIN , which are not supported by all SQL versions .

Example 1: SQL LEFT JOIN example

To see the difference, run the same query using LEFT JOIN. The query is identical except for the addition of the LEFT keyword.

SELECT Customer.CustName, Product.PrdtName
  FROM Customer
  LEFT JOIN Product
  ON Customer.ID = Product.ID;

The rows from the left table, Customer, are all retained in this situation, and when data from the owners table is missing, it is filled with NULL.

Example 2: SQL RIGHT JOIN example

If you run the same query with the RIGHT JOIN operator, you'll get a different result.

SELECT Customer.CustName, Product.PrdtName
  FROM Customer
  RIGHT JOIN Product
  ON Customer.ID = Product.ID;

All rows from the right table, owners, are maintained in this situation, and any missing values are filled with NULL

Output: The output of above query is

There appears to be an Customer who does not have a Product Order.

Example 3: SQL FULL JOIN example

You could do the same query again, using FULL JOIN.

SELECT Customer.CustName, Product.PrdtName
  FROM Customer
  FULL JOIN Product
  ON Customer.ID = Product.ID;

SQL Difference Between INNER JOIN And OUTER JOIN CLAUSE

There are 13 main distinguish in inner join & outer join in below table:-