You are here
Home > Sql Server > SQL JOIN

SQL JOIN

SQL JOIN

  • Sql join is used to combine data from two or more tables in a database.
  • Join keyword is used to join one (self-table) or more tables in sql query.
  • According to ANSI SQL-89 syntax for joining two tables:
  • select [column-list]

    from table_name1,table_name2

    where <where predicate>

  • According to ANSI SQL-92 syntax for joining two tables:
  • select [column-list]

    from table_name1 join table_name2

    on <on predicate>

  • There are different types of joins in SQL.
  1. Inner join
  2. Outer join
  3. Cross join
  4. Self join
  • There are three types of Outer join:
  1. Left Outer join
  2. Right Outer join
  3. Full Outer join
  • Database table: Customer

Cust_id

Firstname Lastname City Phone
1 Pari Patel Anand

12345

2

Prince Shah Pune 11224
3 Anil Patel Anand

10987

4

Jiya Joshi Mumbai 12312
5 Tina Shah Surat

45678

6

Sita Parmar Baroda

42036

  • Here, Cust_id is primary key.
  • Database table: Order

Order_id

Order_no Cust_id
1 11228

2

2

22337 2
3 33446

3

4

44555

3

5

55664

4

  • Here, Cust_id is foreign key.
  • Example-1: Using where clause
  • select Firstname, Lastname, City,

    Order_id, Order_no

    from Customer, Order

    where Order.Cust_id = Customer.Cust_id;

  • Output:

Firstname

Lastname City Order_id Order_no
Prince Shah Pune 1

11228

Prince

Shah Pune 2 22337
Anil Patel Anand 3

33446

Anil

Patel Anand 4 44555
Jiya Joshi Mumbai 5

55664

 

  • Example-2: Using ON clause and join keyword
  •  

    select Customer.Firstname, Customer.Lastname, Customer.City, Order.Order_id, Order.Order_no

    from Customer

    join Order

    on Customer.Cust_id= Order.Cust_id;

  • Output:

Firstname

Lastname City Order_id Order_no
Prince Shah Pune 1

11228

Prince

Shah Pune 2 22337
Anil Patel Anand 3

33446

Anil

Patel Anand 4 44555
Jiya Joshi Mumbai 5

55664

 

  • We can also use aliases to reference column name.
  • Syntax:
  • select alias_name.col1, alias_name.col2, alias_name.col3,…., alias_name.colN

    from table_name1 as alias_name

    join table_name2 as alias_name

    on <on predicate>

  • You can also write alias this way:
  • Syntax:
  • select alias_name.col1, alias_name.col2, alias_name.col3,…., alias_name.colN

    from table_name1  alias_name

    join table_name2  alias_name

    on <on predicate>

  • Example-3: Using alias
  • select C.Firstname, C.Lastname, C.City, O.Order_id, O.Order_no

    from Customer C

    join Order O

    on C.Cust_id= O.Cust_id;

  • Output:

Firstname

Lastname City Order_id Order_no
Prince Shah Pune 1

11228

Prince

Shah Pune 2 22337
Anil Patel Anand 3

33446

Anil

Patel Anand 4 44555
Jiya Joshi Mumbai 5

55664

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

 

Leave a Reply

Top