You are here

SQL :Join

sql

SQL JOIN

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.

         Syntax:

Select <col1>,<col2>,<coln> from <table1> join <table2> ON     <join_condition>;

  • According to ANSI SQL-89: Tables are joined by ‘,(comma)’ in from clause.

         Syntax:

Select <col1>,<col2>,<coln> from <table1> , <table2>  where     <where_condition>;

Types of SQL joins:

Example:

Table: employee(emp_id is primary key)

emp_id

firstname Lastname

1

Dan

patel

2 Riya

Patel

3

Priya

Joshi

4

Aisha

Patel

Table: product(pro_id is primary key and e_id is foreign key)

pro_id

Product_name e_id

1

Pens 2
2 Books

4

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;

Output:

emp_id

firstname lastname product_name
2 Riya patel

Pens

4

Aisha patel

Books

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.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top