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"
| Empno |
sal |
dept |
loc |
dname |
| 162715 |
25000 |
10 |
Bangalore |
software |
| 162716 |
28000 |
20 |
Mysore |
H/w |
| 162455 |
12000 |
30 |
Mangalore |
Sales |
Assume emp has deptno like 10,20,30,50 and dept has deptno like 10,20,30,40. Now query should return all the dept which are in emp and dept.
SELECT * FROM emp, dept WHERE emp.deptno(+) = dept.deptno(+);
The above query fails. We should not have (+) on either side. We can use "Full Outer Join" Or use the following technique.
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno (+)
UNION SELECT * FROM emp, dept WHERE emp.deptno (+) = dept.deptno;
The output looks like this.
| Empno |
sal |
dept |
loc |
dname |
| 162715 |
25000 |
10 |
Bangalore |
software |
| 162716 |
28000 |
20 |
Mysore |
H/w |
| 162455 |
12000 |
30 |
Mangalore |
Sales |
| |
|
40 |
ITPL |
Accounts |
| 12345 |
45857 |
|
|
|
Empno 12345 may belong to some other dept that is not in dept table.
It uses the outer join syntax to join the "emp" and "dept" tables based on their "deptno" columns. The (+) symbol indicates an outer join, where the records from the "emp" table will be included in the result set even if there is no match in the "dept" table. The UNION operator combines the result sets of the two SELECT statements.
Summary
It is important to note that the syntax using the "(+)" symbol is specific to Oracle databases and may not work in other SQL databases. In addition, this query does not specify the columns to select, which can lead to potential issues with duplicate column names in the result set. It is always recommended to explicitly specify the columns to select in a query.