SQL FULL JOIN - The Coding Shala

Home >> Learn SQL >> SQL FULL JOIN

SQL FULL JOIN

The SQL FULL JOIN or SQL FULL OUTER JOIN returns all the records when there is a match on either left or the right table. The SQL FULL OUTER JOIN is a combination of a LEFT OUTER JOIN and RIGHT OUTER JOIN. 

SQL FULL OUTER JOIN Syntax

The basic syntax of the FULL JOIN is as follows - 

SELECT table1.column1, table2.column2, ..  
 FROM table1  
 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;  

SQL FULL OUTER JOIN Example

The following two tables are used for the example -

Table 1. 'emp' table 

 emp_id   emp_name   city   dept_no   salary   
  1        Akshay      Pune   101    50000   
  3         Nikhil      Pune   101    51000   
  5         Mohit      Delhi   103    40000   
  6         Shubham    Surat   105    42000   
  7         Akash      Mumbai  106    45000   

Table 2. 'dept' table 

dept_no     dept_name      total_emp  
 101          Product Dev       50  
 102          Consulting       100  
 103          Product Consult   20  
 104          Marketing        150  
 105          Sales            250  

The following SQL query is an example of SQL FULL JOIN - 

select emp.emp_id, emp.emp_name,   
  dept.dept_no, dept.dept_name   
  from emp   
  full outer join dept on emp.dept_no = dept.dept_no;   

Other Posts You May Like

• SQL LEFT JOIN
• SQL INNER JOIN
• SQL SELF JOIN
• SQL CROSS JOIN
• SQL JOINS

Please leave a comment below if you like this post or found some error, it will help me to improve my content.