Joins In Sql Server

JOINS

• SQL JOINS are used to retrieve data from two or more tables and show that data in a single table on the basis of the JOIN condition.
• A join is actually performed by the where clause which combines the specified rows of tables.
• In SQL, tables are related to each other with keys(Primary key, Candidate key, Super key). Primary key is a column that contain unique value for each row

JOIN: TYPES

• Inner join
  • Equi join
    • Natural join
    • Cross join
  • Non-equi join

• Outer join
  • Left outer
  • Right outer
  • Full outer
• Self join
•  
•  
• INNER JOIN
  INNER JOIN will display all the records that have matched.
  Syntax:
  Select colname(s) from table_name1 inner join table_name2 using (colname)
  Reference Table 1: “student” table           * RollNo is the primary Key
  

  RollNo	StudentName	Marks
  1	abhi	78
  2	sunny	88
  3	Rajesh	300
  4	Rahul	400

  Reference Table 2: “project” table                                           * ProjId is the primary Key
  

  ProjID	ProjName	RollNo
  3	Java Beans	1
  5	Asp.Net	2
  8	Spring	3
  14	Hibernate	4

  Example:
  SQL> select StudentName, Marks, ProjName from student inner join project using(RollNo);
  Output:
  

  StudentName	Marks	ProjName
  abhi	78	Java Beans
  sunny	88	Asp.Net
  Rajesh	300	Spring
  Rahul	400	Hibernate

  
  INNER JOIN: TYPES
  EQUI JOIN
  EQUI JOIN is a join which contains an ‘=’ operator in the joins condition.
  Syntax:
  Select colname(s) from table_name1, table_name2 where table_name1.colname = table_name2. colname;
  Example:
  SQL> select StudentName, Marks, ProjName from student, project where student.RollNo= project. RollNo;
  Output:
  

  StudentName	Marks	ProjName
  abhi	78	Java Beans
  sunny	88	Asp.Net
  Rajesh	300	Spring
  Rahul	400	Hibernate

  
  NATURAL JOIN
  Natural join compares all the common columns.
  Syntax:
  Select colname(s) from table_name1 natural join table_name2;
  Example:
  SQL> select RollNo, StudentName, Marks, ProjName from student natural join project;
  Output:
  

  RollNo	StudentName	Marks	ProjName
  1	abhi	78	Java Beans
  2	sunny	88	Asp.Net
  3	Rajesh	300	Spring
  4	Rahul	400	Hibernate

  
  CROSS JOIN
  CROSS JOIN will gives the cross/cartesion product.
  Syntax:
  Select colname(s) from table_name1 cross join table_name2;
  Example:
  SQL> select RollNo, StudentName, Marks, ProjName from student cross join project;
  Output:
  

  RollNo	StudentName	Marks	ProjName
  1	abhi	78	Java Beans
  2	sunny	88	Java Beans
  3	Rajesh	300	Java Beans
  4	Rahul	400	Java Beans
  1	abhi	78	Asp.Net
  2	sunny	88	Asp.Net
  3	Rajesh	300	Asp.Net
  4	Rahul	400	Asp.Net
  1	abhi	78	Spring
  2	sunny	88	Spring
  3	Rajesh	300	Spring
  4	Rahul	400	Spring
  1	abhi	78	Hibernate
  2	sunny	88	Hibernate
  3	Rajesh	300	Hibernate
  4	Rahul	400	Hibernate

  
  NON EQUI JOIN:
  NON EQUI JOIN contains an operator other than ‘=’ in the joins condition.
  Syntax:
  Select colname(s) from table_name1, table_name2 where table_name1.colname> table_name2. colname;
  Example:
  SQL> select StudentName, Marks, ProjName from student, project where student.RollNo> project. RollNo;
  Output:
  

  StudentName	Marks	ProjName
  sunny	88	Java Beans
  Rajesh	300	Asp.Net
  Rahul	400	Spring

   
• OUTER JOIN
  Outer join gives the non-matching records along with matching records.
  OUTER JOIN: TYPES
  LEFT OUTER JOIN
  LEFT OUTER JOIN will display the all matching records and the records which are in left hand side table those that are not in right hand side table.
  Syntax:
  Select colname(s) from table_name1 left outer join table_name2 on table_name1.colname= table_name.colname;
  Reference Table 1: “student” table                                      * RollNo is the primary Key
  

  RollNo	StudentName	Marks
  1	abhi	78
  2	sunny	88
  3	Rajesh	300
  4	Rahul	400

  
  
  Reference Table 2: “project” table                                        * ProjId is the primary Key
  

  ProjID	ProjName	RollNo
  3	Java Beans	1
  5	Asp.Net	2
  8	Spring	3

  
  Example:
  SQL> select StudentName, Marks, ProjName from student left outer join project on student.RollNo= project. RollNo;
  Output:
  

  RollNo	StudentName	Marks	ProjName
  1	abhi	78	Java Beans
  2	sunny	88	Asp.Net
  3	Rajesh	300	Spring
  4	Rahul	400

  
  RIGHT OUTER JOIN
  RIGHT OUTER JOIN will display the all matching records and the records which are in right hand side table those that are not in left hand side table.
  Syntax:
  Select colname(s) from table_name1 right outer join table_name2 on table_name1.colname= table_name.colname;
  Example:
  SQL> select StudentName, Marks, ProjName from student right outer join project on student.RollNo= project. RollNo;
  Output:
  

  RollNo	StudentName	Marks	ProjName
  1	abhi	78	Java Beans
  2	sunny	88	Asp.Net
  3	Rajesh	300	Spring
  			Hibernate

  SQL: FULL OUTER JOIN
  FULL OUTER JOIN will display the all matching records and the non-matching records from both tables.
  Syntax:
  Select colname(s) from table_name1 full outer join table_name2 on table_name1.colname= table_name.colname;
  Example:
  SQL> select StudentName, Marks, ProjName from student full outer join project on student.RollNo= project. RollNo;
  Output:
  

  RollNo	StudentName	Marks	ProjName
  1	abhi	78	Java Beans
  2	sunny	88	Asp.Net
  3	Rajesh	300	Spring
  4	Rahul	400
  			Hibernate

   
• SELF JOIN
  Joining the table itself is called self join.
  Syntax:
  Select colname(s) from table_name t1, table_name t2 where t1.colname=t2.colname;
  Reference Table 1: “student” table                                      * RollNo is the primary Key
  

  RollNo	StudentName	ProjID	Marks
  1	abhi	2	78
  2	sunny	1	88
  3	Rajesh	4	300
  4	Rahul	3	400

  
  Example:
  SQL> select t1.StudentName, t2.Marks from student t1, student t2 where t1.RollNo=t2.ProjId;
  Output:
  

  StudentName	Marks
  abhi	88
  sunny	78
  Rajesh	400
  Rahul	300