Learn SQL: What are Joins and how to use them with example | MySql

WHAT ARE JOIN In Sql?



SQL; My SQL; SQL Joins

 
Joins are used for combining the row of two or more tables by using a single select statement query. The column of the table that are combine with each other have same datatype.

Suppose we have two tables Emp table and Dept table. We want to join the data so that we get all the information about the employees work in that.

Emp table:


Emp_id Emp_Name Dept_No.
101 A 10
102 B 20
103 C 50

 
Dept table:


Dept_No Dept_Name Dept_loc
10 DA Delhi
20 DB Agra
30 DC Banglore
40 DD Hyedrabad
50 DE Chennai


Aliases : They are used to give a temporary name to the column or table in database.


Example: SELECT a.Emp_id, b.Dept_id FROM Emp a, Dept b WHERE a.Dept_No=b.Dept_No;


Here, we create the "table alias" by giving the short name to the table.  Emp table alias as "a" while Dept Table alias as "b"

Another example:  SELECT a.Emp_id ''A", b.Dept_id"B" FROM Emp a, Dept b WHERE a.Dept_No=b.Dept_No;

Here, we create table alias by giving column a temporary name.



TYPE OF JOINS:


1. INNER JOIN: It retrieves matching record from both the table.



SYNTAX: SELECT col_name FROM table1 or table 2 INNER JOIN table 2 or table 1ON table1.col_name=table2.col_name;


Example:


SQL; My SQL; SQL Joins

 
If you didn't make table or column alias then it will give an error as:


SQL; My SQL; SQL Joins

 
So, it is needed to make alias whether table or column. Above, we use table alias.


2. LEFT OUTER JOIN: It returns all the record from the left table  and null values for the column having no matching records in right table.



SYNTAX: SELECT col_name FROM table1 or table 2 LEFT JOIN table2 or table 1 ON table1.col_name=table2.col_name;


SQL; My SQL; SQL Joins



3. RIGHT OUTER JOIN: It returns all the record from the right table and null values for the column having no matching records in left table.


SYNTAX: SELECT col_name FROM table1 or table 2 RIGHT JOIN table2 or table 1 ON table1.col_name=table2.col_name;


Example:


SQL; My SQL; SQL Joins

 


4. FULL OUTER JOIN: It retrieves all the records from both tables and null values for the null values for the column having no matching records in any of the table (right or left).

SYNTAX: SELECT col_name FROM table1 or table 2 FULL OUTER JOIN table2 or table 1 ON table1.col_name=table2.col_name;


BUT, SQL doesn't support FULL OUTER join. IF you write this query then it will shows an error.


5. SELF JOIN: Whenever a row of table are compared with another rows of the same table or whenever the table is join with itself then self join is required.

Example:

         | id   | Name | Boss_id |

         |   1  | A        |       3      |
         |   2  | B        |       5      |
         |   3  | C        |       4      |
         |   4  | D        |       1      |
         |   5  | E        |       2      |


In above example, the id of the employees and their boss are given and we have to find that who is the boss of employees. This is done by SELF JOIN as:

SELECT c1.Name, c2.Name AS Boss FROM clown_info c1 INNER JOIN clown_info c2 ON c1.Boss_id=c2.id;

Here, c1 and c2 are the alias.


SQL; My SQL; SQL Joins

Previous

1 comment:

  1. I’m impressed, I have to say. Actually hardly ever do I encounter a weblog that’s both educative and entertaining, and let me let you know, you've gotten hit the nail on the head. Your concept is excellent; the difficulty is one thing that not sufficient persons are talking intelligently about. I'm very blissful that I stumbled across this in my seek for something relating to this. seo ppc

    ReplyDelete

Powered by Blogger.