WHAT ARE JOIN In Sql?
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;
If you didn't make table or column alias then it will give an error as:
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;
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:
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.
Previous
No comments: