Sometimes it is necessary to work with multiple tables using single query. Joins are used to achieve this. Tables are joined on columns that have the same data type and length in the tables.
- Tables in a database can be related based on primary and foreign keys. A primary kay is a column with a unique value for each row.
Two forms of join:
- According to ANSI SQL-92: Tables are joined by ’JOIN’ operator in ‘FROM’ clause.
Select <col1>,<col2>,<coln> from <table1> join <table2> ON <join_condition>;
- According to ANSI SQL-89: Tables are joined by ‘,(comma)’ in from clause.
Select <col1>,<col2>,<coln> from <table1> , <table2> where <where_condition>;
Types of SQL joins:
Table: employee(emp_id is primary key)
Table: product(pro_id is primary key and e_id is foreign key)
Retrieve the employee_id, firstname, lastname and product name handle by the employee.
According to ANSI SQL-92:
select e.emp_id, e.firstname, e.lastname, p.product_name from employee as e join product as p on e.emp_id=p.e_id;
According to ANSI SQL-89:
select e.emp_id, e.firstname, e.lastname, p.product_name from employee as e, product as p where e.emp_id=p.e_id;
Advantages of joins:
- The main advantage of join is that it executes faster than subquery.
- Using joins we can retrieve rows from two or more tables.
- Using self joins we can perform operation in the same table like finding who is manager of particular employee.
- Using cross joins we can perform cartesian product of two tables. And if we provide where condition in cross join then it behave like a inner join.
Disadvantages of joins:
- The main disadvantage of using join is that it is not easy to read as compared to subquery.
- it is confusing to decide which type of join is appropriate for particular condition.
Keep Visiting TechwithR.com for more upcoming top technical articles.