Introduction
In Oracle SQL, the plus sign (+) is used as a shorthand notation for specifying an outer join between two tables. The outer join retrieves all the records from one table and only the matching records from the other table. The (+) sign indicates which table's records must be included in the result set, even if there is no match in the other table.
In short, Symbol (+) says to add null values for the table's columns, which have matching columns in the other tables.
Example of an outer join in Oracle SQL, using the (+) operator.
SELECT a.empno,a.sal,b.dept,b.loc,dname FROM emp a,emp b WHERE a.deptno(+) = b.deptno;
This query retrieves all the records from table "a" and only the matching records from table "b", where the "deptno" column in the table "a" matches the "deptno" column in table "b". If there is no match in table "b", the columns in table "b" will contain NULL values in the result set.
This above query will return the deptno 40 information even though no employee belongs to dept 40.
| Empno | sal | dept | loc | dname |
| 162715 | 25000 | 10 | Bangalore | software |
| 162716 | 28000 | 20 | mysore | H/w |
| 162455 | 12000 | 30 | Mangalore | Sale |
| 40 | ITPL | Accounts |
If you don't use the (+) symbol, the join will default to an inner join, retrieving only the records that match both tables.
The last record won't be displayed if we don't specify the (+) symbol in the query.
Example of an inner join in Oracle SQL.
SELECT a.empno, a.sal, b.dept, b.loc, dname FROM emp a, emp b WHERE a.deptno = b.deptno;
This query will retrieve only the records that have matching values in both tables "a" and table "b"

Join the conversation! Your thoughts help the community grow.