What Does (+) Do in Oracle SQL

What does (+) do in Oracle SQL?

It is not recommended. See this previous answer

Difference between Oracle's plus (+) notation and ansi JOIN notation?

What does / do in PL/SQL?

"/" executes the sql command in the current buffer. It similar to GO of SQL Server

What is the meaning of '@' symbol in Oracle SQL?

It refers to a non-local table, the bit behind the @ is the db descriptor.

select * from question_answer@abcd where id = '45'

Means select not from the local question_answer table, but from the table on the db designated as abcd. The keyword to google for is dblink

Oracle (+) Operator

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

---

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

Oracle: What does `(+)` do in a WHERE clause?

Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by people who learned it first, since they like that it makes their code shorter.

Best not to use it though, for readability's sake.

What does symbol / do at end of sql scripts?

From the documentation:

Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

The buffer has no command history and does not record SQL*Plus commands.

What does || ' ' || mean in oracle SQL

The SQL || operator allows you to concatenate 2 or more strings together.

Example:
varchar 1: Harry -
Varchar 2: Mueller

You can concatenate this two with || operator:

Select 'Harry' || 'Mueller' from dual:

output:

HarryMueller

But with a space you can seprate these two. You can add a space between two varchar with a ' ' :

Select 'Harry' || ' ' || 'Mueller' from dual:

output:

Harry Mueller

What does the colon sign : do in a SQL query?

That's called a bind variable in Oracle.

what's the name for ":"?

Colon.

What does a (+) sign mean in an Oracle SQL WHERE clause?

This is an Oracle-specific notation for an outer join. It means that it will include all rows from t1, and use NULLS in the t0 columns if there is no corresponding row in t0.

In standard SQL one would write:

SELECT t0.foo, t1.bar
  FROM FIRST_TABLE t0
 RIGHT OUTER JOIN SECOND_TABLE t1;

Oracle recommends not to use those joins anymore if your version supports ANSI joins (LEFT/RIGHT JOIN) :

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions […]

---

Related Topics