Collections:
Left Outer Join with the WHERE Clause in Oracle
How To Write a Left Outer Join with the WHERE Clause in Oracle?
✍: FYIcenter.com
If you don't want to use the LEFT OUTER JOIN ... ON clause to write a left outer join, you can use a special criteria in the WHERE clause as "left_table.column = right_table.column(+)". The select statement below is an example of a left outer join written with the WHERE clause:
SQL> set NULL 'NULL' SQL> SELECT d.department_name, e.first_name, e.last_name 2 FROM departments d, employees e 3 WHERE d.manager_id = e.employee_id(+); DEPARTMENT_NAME FIRST_NAME LAST_NAME -------------------- -------------------- -------------- Administration Jennifer Whalen Marketing Michael Hartstein Purchasing Den Raphaely Human Resources Susan Mavris Shipping Adam Fripp IT Alexander Hunold ...... Treasury NULL NULL Corporate Tax NULL NULL Control And Credit NULL NULL Shareholder Services NULL NULL Benefits NULL NULL Manufacturing NULL NULL ......
Note that a left outer join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.
The extra rows returned from the left outer join in this example represents departments that have no manager IDs.
⇒ Name Query Output Columns in Oracle
⇐ Inner Join with the WHERE Clause in Oracle
2019-10-18, 1512🔥, 0💬
Popular Posts:
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...