SQL Left Outer Join in Oracle SQL | Oracle SQL Tutorials -21

Hi,

I will explain SQL Left Outer Join in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

SQL Inner Join in Oracle SQL | Oracle SQL Tutorials -20

 

 

 

The Left Outer Join

The Left Outer Join is used to list the records from the left table and the matched records from the right table.

 

 

Left (Outer) JOIN Syntax

Left Outer Join syntax is as follows.

 

SELECT column1,column2,column(n)...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

You can use left outer join as follows. Left outer join and left join clause run the same as follows.

SQL> SELECT first_name,e.last_name, e.department_id, d.department_name
  2     FROM   hr.employees e LEFT OUTER JOIN hr.departments d
  3     ON   (e.department_id = d.department_id) where e.department_id>80 ;

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Neena                Kochhar                              90 Executive
Lex                  De Haan                              90 Executive
Nancy                Greenberg                           100 Finance
Daniel               Faviet                              100 Finance
John                 Chen                                100 Finance
Ismael               Sciarra                             100 Finance
Jose Manuel          Urman                               100 Finance
Luis                 Popp                                100 Finance
Shelley              Higgins                             110 Accounting
William              Gietz                               110 Accounting

10 rows selected.

SQL> 
SQL> 
SQL> SELECT first_name,e.last_name, e.department_id, d.department_name
  2     FROM   hr.employees e LEFT JOIN hr.departments d
  3     ON   (e.department_id = d.department_id) where e.department_id>80 ;

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Neena                Kochhar                              90 Executive
Lex                  De Haan                              90 Executive
Nancy                Greenberg                           100 Finance
Daniel               Faviet                              100 Finance
John                 Chen                                100 Finance
Ismael               Sciarra                             100 Finance
Jose Manuel          Urman                               100 Finance
Luis                 Popp                                100 Finance
Shelley              Higgins                             110 Accounting
William              Gietz                               110 Accounting

10 rows selected.

SQL>

fe

 

 

 

Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *