Joinings in Sql

SQL EQUI JOIN

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.
You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.

Syntax 

T agents.agent_name,customer.cust_name,  customer.cust_city  FROM agents,customer  

WHERE agents.working_area=customer.cust_city; 

SQL NON EQUI JOIN

The SQL NON EQUI JOIN uses comparison operator instead of the equal sign like >, <, >=, <= along with conditions.

Syntax

SELECT a.ord_num,a.ord_amount,b.cust_name,b.working_area   FROM orders a,customer b   WHERE a.ord_amount BETWEEN b.opening_amt and b.opening_amt;

SQL INNER JOIN

 

The INNER JOIN will select all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

SELECT foods.item_name,foods.item_unit,   company.company_name,company.company_city   FROM foods   INNER JOIN company   ON foods.company_id =company.company_id;  

SQL NATURAL JOIN

The SQL NATURAL JOIN is a type of equi-join and is structured in such a way that, columns with same name of associate tables will appear once only.

Syntax

SELECT * FROM foods NATURAL JOIN company;

SQL CROSS JOIN

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.
If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause. 

SQL OUTER JOIN

The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only.

The subtypes of SQL OUTER JOIN

• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN

Syntax

SELECT  company.company_name,company.company_id,foods.company_id foods.item_name,foods.item_unit FROM company, foods WHERE company.company_id = foods.company_id(+);

SQL LEFT JOIN

The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table before the JOIN clause.

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM   company LEFT OUTER JOIN foods ON company.company_id = foods.company_id;

SQL RIGHT JOIN

The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table written after the JOIN clause.

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM   company RIGHT OUTER JOIN foods ON company.company_id = foods.company_id;

 

SQL FULL OUTER JOIN

The FULL OUTER JOIN will return all rows, as long as there's matching data in one of the tables. It includes all the rows from both the participating tables and does not select either the LEFT or RIGHT table from the JOIN key word.
The FULL OUTER JOIN combines the results of both left and right outer joins. When no matching rows exist for rows on the left side of the JOIN key word, NULL values will be returned from the result set on the right. On the other hand , when no matching rows exist for rows on the right side of the JOIN key word, NULL values will be returned from the result set on the left.

SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM   company FULL OUTER JOIN foods

ON company.company_id = foods.company_id;

SQL SELF JOIN

A SELF JOIN is another type of join in sql which is used to join a table to itself, specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

SELECT a.company_name,b.company_name,a.company_city FROM company a, company b

WHERE a.company_city=b.company_city AND a.company_name<>b.company_name;