Joining two tables in MYSQL

 Joining two tables in MYSQL 

·         Joining the two or tables in MySQL with the JOIN clause that combines the rows from several tables based on a column that is common to all the tables and has a relationship between them.

• ·         It creates a set of rows in a temporary table.
• ·         JOIN keeps the base tables (structure and data) unchanged.
• ·         JOINs are faster than a subquery.
• ·         In JOINs the RDBMS calculates an execution plan, that can predict, what data should be loaded and processed result which process saves some times, processing.
• ·         A JOIN has checked conditions first and then put them into table and displays.
• ·         When joins are used, there should be a connection between two or more two tables and each table has a relation with the other.

 

They are two types of SQL JOINS

1) SQL EQUI JOIN :

·         a simple SQL join uses the equal sign(=) as the comparison operator for the condition. It has two types - SQL Outer join and SQL Inner join.

 

2) SQL NON-EQUI JOIN :

·         a join uses a comparison operator other than the equal sign like >, <, >=, <= with the condition.

 

SQL EQUI JOIN :

·         classified into two types - INNER JOIN and OUTER JOIN

1. SQL INNER JOIN

·         This type of EQUI JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.

2. SQL OUTER JOIN

·         This type of EQUI JOIN returns all rows from one table and only those rows from the secondary table where the joined condition is satisfying i.e. the columns are equal in both tables.

 IN a JOIN query, the required information is:

a) The name of the tables
b) Name of the columns of two or more tables, based on which a condition will perform.

 Syntax:

FROM table1 join_type table2 [ON (join_condition)]

 

Example:

 To join two tables 'company' and 'foods', the following SQL statement can be used :

SELECT  company.company_id, company.company_name, foods.item_id, foods.item_name FROM company,foods;

Output:

COMPANY            COMPANY_NAME              ITEM_ID       ITEM_NAME

18                        Order All                                         1            Chex Mix

18                        Order All                                         6        Cheez-It

15                       Jack Hill Ltd                                    1        Chex Mix

15                       Jack Hill Ltd                                    6        Cheez-It

16                       Akas Foods                                       1        Chex Mix

16                       Akas Foods                                       6        Cheez-It

.........

.........

.........

 

also, perform EQUI JOIN by using the JOIN keyword followed by the ON keyword and then specifying the names of the columns along with their associated tables to check equality.

Syntax:

SELECT column_list FROM table1, table2.... WHERE table1.column_name =

table2.column_name; 

or

SELECT * FROM table1 JOIN table2 [ON (join_condition)]

Pictorial representation:

Example:

To get agent name column from agents table and cust name and cust city columns from customer table after joining said two tables with the following condition -

the working area of agents and customer city of customer table must be the same.

SQL Code:

SELECT agents.agent_name, customer.cust_name,  customer.cust_city  FROM agents, customer WHERE agents.working_area = customer.cust_city;

Output:

AGENT_NAME                    CUST_NAME                                CUST_CITY

Ravi Kumar                               Ravindran                                Bangalore

Ramasundar                               Ravindran                                Bangalore

Subbarao                                 Ravindran                                Bangalore

Ravi Kumar                               Venkatpati                               Bangalore

Anderson                                 Winston                                  Brisban

Santakumar                               Yearannaidu                              Chennai

...........

NON EQUI JOIN

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

Syntax:

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

Pictorial representation:

Example:

To get order number and order amount columns from orders table aliased as 'a' and customer name and working area columns from customer table aliased as 'b' after joining said two tables with the following condition -

order amount of orders table matches any of the opening amounts of customer table.

SQL Code:

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;

Output:

ORD_NUM    ORD_AMOUNT    CUST_NAME      WORKING_AREA

   200110             3000          Micheal                New York

   200101             3000          Micheal                New York

   200108             4000          Cook                    London

   200119             4000                     Cook                  London

   200108             4000                     Karl                      London

 

***********************************************************************************