Avatar of shacho
shacho
 asked on

Mysterious Curly Brackets (Braces)

I'm new to Oracle SQL - forgive my ignorance.
I'm trying to understand a fragment of SQL I picked up from a query.
Notice that the last join parameter is contained in curly brackets.
I can't figure out why.
As I understand it {PARAM3} is a commonly used wildcard for the SQL query tool we use.  In a SELECT or WHERE segment of SQL, this wildcard will be replaced with an actual string before it gets sent to the Oracle interpreter.  So {PARAM3} will have turned into something like 123 by the time it gets to the Oracle server.  But this {PARAM3} is in a join.  WHERE A.PARAM3 = 123 would be fine, of course, but LEFT JOIN TBL_B ON A.PARAM3 = 123 makes no sense (to me).  Am I missing something?

SELECT A.ID, B.PRICE
FROM TBL_A A
LEFT JOIN TBL_B B
ON A.PARAM1 = B.PARAM1
AND A.PARAM2 = B.PARAM2
AND A.PARAM3 = B.{PARAM3}
Oracle DatabaseSQL

Avatar of undefined
Last Comment
shacho

8/22/2022 - Mon
Wasim Akram Shaik

-->Mysterious Curly Brackets {

I don't think its a valid syntax for join in Oracle, I am sure if you try to execute it that way you will get an error,

Even if you go by oracle's proprietary syntax of using joins, I have seen only () can be used in outer joins, {} are not used in oracle joins upto oracle 10g, I am sure that there are no modifications on joins in braces in 11g too.
Wasim Akram Shaik

Also

-->In a SELECT or WHERE segment of SQL, this wildcard will be replaced with an actual string before it gets sent to the Oracle interpreter.

yes, starting from 10g, oracle introduced a feature User-Defined Quote Character, where in you can use { as a delimiter to quote, but in case of join, there are no such delimiters
Guy Hengel [angelIII / a3]

I have not seen the { }  stuff so far but as you indicate that it's being replaced before being send to oracle, that part does not actually matter for the question.
having additional conditions in a left join, with fixed values, is not incorrect and can make perfectly sense.
consider a one-to-many relation ship, where this additional field indicates the type of relation. for example, person and communication, with that field being communication-type, having values like SMS, EMAIL, FAX, ...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PortletPaul

>>"LEFT JOIN TBL_B ON A.PARAM3 = 123 makes no sense (to me). "

mmm, why not? It may not make sense for those tables/fields but the concept is fine.

from InsuredParty IP
left join InsuredPartyDetails IPD on IP.ID = IPD.FK and IPD.Type = 'spouse'

only a certain type of record in the outer joined table is wanted, which can be there or not (hence the outer join)

also note:

from InsuredParty IP
left join InsuredPartyDetails IPD on IP.ID = IPD.FK
WHERE IPD.Type = 'spouse'

would be wrong if we want records without a spouse, it would require more

from InsuredParty IP
left join InsuredPartyDetails IPD on IP.ID = IPD.FK
WHERE IPD.Type = 'spouse' OR IPD.Type IS NULL

Gerwin Jansen

>> the SQL query tool we use
Which tool is that exactly? Do you have a reference to it?
shacho

ASKER
>[Guy] as you indicate that it's being replaced before being send to oracle, that part does not actually matter for the question.
You are correct.

>[Guy] having additional conditions in a left join, with fixed values, is not incorrect and can make perfectly sense.
>[Paul] mmm, why not? It may not make sense for those tables/fields but the concept is fine.

OK - you both note that it's OK to use a literal value in a Join.  I've never seen this before so I don't know how to interpret it, i.e. I've only ever seen field names used in joins.  So what does it mean, exactly?  Can I expect the same output from these two statements?

SELECT A.ID, B.PRICE
FROM TBL_A A
LEFT JOIN TBL_B B
ON A.PARAM1 = B.PARAM1
AND A.PARAM2 = B.PARAM2
AND A.PARAM3 = 123

SELECT A.ID, B.PRICE
FROM TBL_A A
LEFT JOIN TBL_B B
ON A.PARAM1 = B.PARAM1
AND A.PARAM2 = B.PARAM2
WHERE A.PARAM3 = 123

If so, why do it this way?  Efficiency?

If not, how would the results differ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shacho

ASKER
ALSO...

I should note a mistake in my original, sanitized SQL.

The last line in my posting was:
AND A.PARAM3 = B.{PARAM3}

In fact it should be:
AND A.PARAM3 = {PARAM3}

The right side is not qualified in the original SQL.
When it gets to Oracle, it will just be a literal, like 123.
Guy Hengel [angelIII / a3]

LEFT JOIN ...  ON ... AND XXX
will make the additional condition XXX will be evaluated as part of the join condition, and not after the join. effectively, putting:
LEFT JOIN ...  ON ... WHERE XXX
in this case makes it a implicit INNER JOIN !
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
shacho

ASKER
AH!  I get it.  sqlfiddle << very helpful.  Thanks for that.

Just to be sure....

I understand that the first query is functionally equivalent to this:
SELECT
  e.*
, s.*
FROM employee AS e
LEFT JOIN 
(
  select emp_id, spouse_name, is_current
  from spouse
  where is_current = 1
) as s
ON e.id = s.emp_id

Open in new window


1 more question...

Select e.*, s.*  >> What's happening to s.id in the output?  Is it getting tossed out by the interpreter?

Mike
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

>>" I understand that the first query is functionally equivalent  ..."
yes, that's correct

>>" Select e.*, s.*  >> What's happening to s.id in the output?  "
this is a "feature" at sqlfiddle which will not repeat a column heading i the result, so only the first column known as [id] is displayed
workaround is to specify column aliases

mind you, e.*,s.* is not recommended practice for production level coding
shacho

ASKER
Ah - I see.  Thanks for all your help!!

Mike