Tip of the Day ( Sunday, November 02, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

Right Outer Join (ANSI Syntax) (Video: https://youtube.com/shorts/4_wj0UgtqsE?feature=share )

A RIGHT OUTER JOIN returns all the rows from the table which is on the right side of the JOIN clause and returns the matching rows from the table which is on the left side of the JOIN clause.
For the non-matching rows, Oracle returns NULLs for the left side table’s columns.

Example:

SELECT d.department_id, d.department_name, location_id, l.city
  FROM departments d RIGHT OUTER JOIN locations l
 USING(location_id);

Since locations table is on the right side of the JOIN clause, this query will list all the rows from the locations table columns. 

The departments table is on the left side of the JOIN clause. So, only the matching rows (matched by location_id column) will be listed for department table. 

For the non-matching rows from the locations tables, NULL will be listed for departments table.

Below is the output:

DEPARTMENT_ID DEPARTMENT_NAME       LOCATION_ID CITY
------------- ------------------------------ ----------- ----------------------------
   10 Administration             1700 Seattle
       20 Marketing                 1800 Toronto
       30 Purchasing                 1700 Seattle
       40 Human Resources         2400 London
       50 Shipping             1500 South San Francisco
       60 IT                     1400 Southlake
       70 Public Relations     2700 Munich
       80 Sales     2500 Oxford
       90 Executive     1700 Seattle
      100 Finance     1700 Seattle
      110 Accounting     1700 Seattle
      120 Treasury     1700 Seattle
      130 Corporate Tax     1700 Seattle
      140 Control And Credit     1700 Seattle
      150 Shareholder Services     1700 Seattle
      160 Benefits     1700 Seattle
      170 Manufacturing     1700 Seattle
      180 Construction     1700 Seattle
      190 Contracting     1700 Seattle
      200 Operations     1700 Seattle
      210 IT Support     1700 Seattle
      220 NOC     1700 Seattle
      230 IT Helpdesk     1700 Seattle
      240 Government Sales     1700 Seattle
      250 Retail Sales     1700 Seattle
      260 Recruiting     1700 Seattle
      270 Payroll     1700 Seattle
2200 Sydney
2300 Singapore
1000 Roma
1600 South Brunswick
2600 Stretford
2900 Geneva
3100 Utrecht
3200 Mexico City
2800 Sao Paulo
1100 Venice
1900 Whitehorse
1300 Hiroshima
3000 Bern
1200 Tokyo
2000 Beijing
2100 Bombay
43 rows selected.

#####################################################################