SQL - JOINS


Joins in SQL are used to combine rows from two or more tables based on a related column between them. The result of a join operation is a single, unified dataset that includes columns from all the joined tables, allowing you to retrieve and analyze related data from different sources

Types of joins
  1. Inner join
    • equal inner join
    • non inner join
  2. Outer join
    • left join
    • right join
    • full join
  3. cross join
  4. self join
  5. natural join
1)Inner join
  1. equal inner join
  2. It returns data set that have matching values in both tables
	 SQL> select a.ename,a.job,b.deptno,b.dname from emp a inner join
	 dept b on a.deptno=b.deptno;

	 ENAME	   JOB		    DEPTNO            DNAME
	 ------- ---------      ------          -----------
	 SMITH	   CLERK	      20              RESEARCH
	 ALLEN	   SALESMAN	      30               SALES
	 WARD	   SALESMAN	      30               SALES
	 JONES	   MANAGER	      20               RESEARCH
	 MARTIN	   SALESMAN	      30               SALES
	 BLAKE	   MANAGER	      30               SALES
	 CLARK	   MANAGER	      10               ACCOUNTING
	 SCOTT	   ANALYST	      20               RESEARCH
	 KING	   PRESIDENT	      10               ACCOUNTING
	 TURNER	   SALESMAN	      30               SALES
	 ADAMS	   CLERK	      20               RESEARCH
	 JAMES	   CLERK	      30               SALES
	 FORD	   ANALYST	      20               RESEARCH
	 MILLER	   CLERK	      10               ACCOUNTING

	 14 rows selected.
Non Inner Join

It returns data set that have unmatching values in both tables

	SQL> select a.ename,a.job,b.deptno,b.dname from emp a inner join dept b on a.deptno<>b.deptno;

	ENAME	   JOB		   DEPTNO      DNAME
	-------  ---------    --------  --------------
	SMITH	   CLERK	     10       ACCOUNTING
	ALLEN	   SALESMAN	     10       ACCOUNTING
	JONES	   MANAGER	     10       ACCOUNTING
	MARTIN	   SALESMAN	     10       ACCOUNTING
	SCOTT	   ANALYST	     10       ACCOUNTING
	JAMES	   CLERK	     10       ACCOUNTING
	ALLEN	   SALESMAN	     20       RESEARCH
	MARTIN	   SALESMAN	     20       RESEARCH
	CLARK	   MANAGER	     20       RESEARCH
	KING       PRESIDENT	     20       RESEARCH
	JAMES	   CLERK	     20       RESEARCH
	MILLER	   CLERK	     20       RESEARCH
	SMITH	   CLERK	     30       SALES
	JONES	   MANAGER	     30       SALES
	CLARK	   MANAGER	     30       SALES
	SCOTT	   ANALYST	     30       SALES
	KING       PRESIDENT	     30       SALES
	MILLER	   CLERK	     30       SALES
	SMITH	   CLERK	     40       OPERATIONS
	ALLEN	   SALESMAN	     40       OPERATIONS
	JONES	   MANAGER	     40       OPERATIONS
	MARTIN	   SALESMAN	     40       OPERATIONS
	CLARK	   MANAGER	     40       OPERATIONS
	SCOTT	   ANALYST	     40       OPERATIONS
	KING	   PRESIDENT	     40       OPERATIONS
	JAMES	   CLERK	     40       OPERATIONS
	MILLER	   CLERK	     40       OPERATIONS
2)Outer Join

Returns both the matching and unmatching records combining from two or more tables

Left Join

It returns all recorde from the left table and match records from the right

	 
	SQL> select a.name,a.courses,b.courses_id,b.location
	from tesdb a left join tesdb01 b on a.courses_id=b.courses_id;

	 NAME		      COURSES       COURSES_ID     LOCATION
	 -----------    -----------    ------------  --------------
	 arun		     java	      40            madurai
	 babu		     oracle	      50            bangalore
	 naveen		     oracle	      50            bangalore
	 ram		     testing	      70            coimbatore
	 sam		     postgres	      80            chennai
Right Join

It returns all records from the right and the matched records from the left

	 SQL> select a.name,a.courses,b.courses_id,b.location
	 from tesdb a right join tesdb01 b on a.courses_id=b.courses_id;

	 NAME		     COURSES           COURSES_ID       LOCATION
	 -----------   ------------     ----------------   ----------------
	 babu		     oracle			  50                bangalore
	 arun		     java			  40                madurai
	 ram		     testing			  70                coimbatore
	 sam		     postgres			  80                chennai
	 naveen		     oracle			  50                bangalore
							  30                chennai
	 6 rows selected.
Full Join

It returns all records when there is a match in either the left table or right table

	 SQL> select a.name,a.courses,b.courses_id,b.location
	 from tesdb a full join tesdb01 b on a.courses_id=b.courses_id;

	 NAME		   COURSES        COURSES_ID    	LOCATION
	 ----------  	------------   ----------------		------------
						30               chennai
	 arun		     java	    	40               madurai
	 babu		     oracle	    	50               bangalore
	 naveen		     oracle	    	50               bangalore
	 ram		     testing	    	70               coimbatore
	 sam		     postgres	    	80               chennai

	 6 rows selected.
3)Cross Join

Used to combine each row of one table with each row of another table,and return the Cartesian product of the sets of rows from the tables that are joined

	 SQL> select a.name,a.courses,b.courses_id,b.location
	 from tesdb a cross join tesdb01 b;

	 NAME	      COURSES          COURSES_ID       LOCATION
	 --------  -------------    ----------------   ------------
	 babu	     oracle		  30                 chennai
	 babu	     oracle		  40                 madurai
	 babu	     oracle		  50                 bangalore
	 babu	     oracle		  70                 coimbatore
	 babu	     oracle		  80                 chennai
	 arun	     java		  30                 chennai
	 arun	     java		  40                 madurai
	 arun	     java		  50                 bangalore
	 arun	     java		  70                 coimbatore
	 arun	     java		  80                 chennai
	 ram	     testing		  30                 chennai
	 ram	     testing		  40                 madurai
	 ram	     testing		  50                 bangalore
	 ram	     testing		  70                 coimbatore
	 ram	     testing		  80                 chennai
	 sam	     postgres		  30                 chennai
	 sam	     postgres		  40                 madurai
	 sam	     postgres		  50                 bangalore
	 sam	     postgres		  70                 coimbatore
	 sam	     postgres		  80                  chennai
	 Naveen	     oracle		  30                  chennai
	 naveen	     oracle		  40                  madurai
	 naveen	     oracle		  50                  bangalore
	 naveen	     oracle		  70                  coimbatore
	 naveen	     oracle		  80                  chennai

	 25 rows selected.
4)Self Join
  1. A self-join is a type of join where a table is joined with itself
  2. It is used when you want to combine rows from the same table based on a related column or condition
	 SQL> select a.name,a.courses,b.courses_id from tesdb a,tesdb b where a.courses_id=b.courses_id;

	 NAME		     		COURSES		 COURSES_ID
	 -----------   		------------------      ----------
	 babu		               oracle	          50
	 naveen		               oracle	          50
	 arun		               java	          40
	 ram		               testing	          70
	 sam		               postgres	          80
	 babu		               oracle	          50
	 naveen		               oracle	          50
	 7 rows selected.
5)Natural Join

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined

	 SQL> select * from tesdb natural join tesdb01;

	ID        COURSES_ID       NAME         COURSES		LOCATION
	-------  --------------  -----------  -------------  ----------------
	2	   40                arun	java 		madurai


(SQL - Set Operators)