What does (+) mean in SQL? – This article will take you through the common SQL errors that you might encounter while working with sql, sql, sql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
What does the (+) mean in the Where Clauses in this SQL statement?
SELECT p.FIRSTNAME,
p.LASTNAME,
p.LOGINNAME,
a.DESCRIPTION,
a.PERIPHERALNUMBER,
a.SUPERVISORAGENT,
t.ENTERPRISENAME,
t.DIALEDNUMBERID,
sp.FIRSTNAME AS SUPER_FIRSTNAME,
sp.LASTNAME AS SUPER_LASTNAME,
sp.LOGINNAME AS SUPER_LOGINNAME,
sa.PERIPHERALNUMBER AS SUPER_PERIPHERALNUMBER,
sa.SUPERVISORAGENT AS SUPER_SUPERVISORAGENT,
a.SKILLTARGETID,
a.PERSONID,
t.AGENTTEAMID,
sa.SKILLTARGETID AS SUPER_SKILLTARGETID,
sa.PERSONID AS SUPER_PERSONID
FROM C2O.AGENT a,
C2O.PERSON p,
C2O.AGENT_TEAM_MEMBER tm,
C2O.AGENT_TEAM t,
C2O.AGENT sa,
C2O.PERSON sp
WHERE a.PERSONID = p.PERSONID
AND a.SKILLTARGETID = tm.SKILLTARGETID(+)
AND tm.AGENTTEAMID = t.AGENTTEAMID(+)
AND t.PRISUPERVISORSKILLTARGETID = sa.SKILLTARGETID(+)
AND sa.PERSONID = sp.PERSONID(+)
AND a.DELETED = 'N'
AND p.LOGINENABLED = 'Y'
AND SUBSTR(a.PERIPHERALNUMBER,2,3) = 580;
Solution :
In Oracle SQL, this is the deprecated outer join operator.
In Oracle, the (+) specifies that the join is an outer join (instead of an inner join as this implicit join syntax usually implies).
Making it an outer join means that the row should be included in the results even if that particular item is null.
In old Oracle versions, this means an OUTER JOIN.
It is an outer join operator in sql server it would be *= and =*
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.