MySQL : Shivmohan : explain join operations

[DevShivmohan]

All join operations perform between these two tables personal and professional

• Note:- To perform join operations make sure in two tables at least anyone column name and his values should be same like in this below screenshots ID field same in both tables; Let table1 personal and table2 professional after that understand below join operations;

• 1. Equi Join:- Equi join performs equality of given conditions like equal(=) sign means which record depend on equality you can use in where clause only equal(=) operator not for any other;

Syntax- SELECT column_list
FROM table1, table2.... WHERE table1.column_name = table2.column_name;

Example- SELECT personal.id,personal.name,professional.dept,professional.work FROM personal,professional WHERE personal.id=professional.id;

OR

Syntax- SELECT column_list FROM table1
JOIN table2 [ON (join_condition)]

Example- SELECT personal.name,personal.address,professional.dept,professional.work FROM personal JOIN professional ON personal.id=professional.id;

• 2. Non-Equi Join- NON-EQUI JOIN performs a JOIN using a comparison operator other than equal(=) sign like >, <, >=, <= with conditions.

Syntax- SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> | < | >= | <= ] table_name2.column;

Example- SELECT * FROM personal,professional WHERE personal.id < professional.id;

In this query personal.id less then professional.id that record are joined

[DevShivmohan]

Join multiple table at a time

SELECT customerName, customercity, customermail, ordertotal,salestotal
FROM onlinecustomers AS c
   INNER JOIN
   orders AS o
   ON c.customerid = o.customerid
   LEFT JOIN
   sales AS s
   ON o.orderId = s.orderId
   WHERE s.salesId IS NULL

[DevShivmohan]

Join operations using alices

select * from user_master as um join user_role_map as urm on um.user_id=urm.user_id+.....+where urm.role_id=5;

[DevShivmohan]

Note

Equi join = Natural join = Inner join these are same equality;

Natural join syntax select * from <table_name1> natural join <table_name2>;

Inner join syntax select * from <table_name1> inner join <table_name2> on <table_name1>.<column_name>=<table_name2>.<column_name>;

Cross join syntax select * from <table_name1> cross join <table_name2>; If we have two tables every table has 5 records then result of cross join *55** [25] records;

Outer join

• Left outer join

  • In left outer join first we have to consider as a left table and as a right table. Then we have to got result full of the left table and in the right table data shows only whose condition has satisfied.
    Syntax select * from <table_name1> left outer join <table_name2> on <table_name1>.<column_name>=<table_name2>.<column_name>;

• Right outer join

  • In right outer join first we have to consider as a left table and as a right table. Then we have to got result full of the right table and in the left table data shows only whose condition has satisfied.
    Syntax select * from <table_name1> right outer join <table_name2> on <table_name1>.<column_name>=<table_name2>.<column_name>;

• Full outer join

  • In full outer join first we have to consider as a left table and as a right table. Then we have to got full result of the left table as well as right both either condition has satisfied or not.
    Syntax select * from <table_name1> full outer join <table_name2> on <table_name1>.<column_name>=<table_name2>.<column_name>;