Mysterious Curly Brackets (Braces)

shacho
shacho used Ask the Experts™
on
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}
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
-->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.
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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, ...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"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, EE MVETopic Advisor
Most Valuable Expert 2016

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

Author

Commented:
>[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?

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 !
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
With this sample data there are 3 employee only 2 are married, 1 (fred) has been divorced (pauline). I want a list of employees and their current spouse, I expect 3 employees in that list.
Sample Data:
CREATE TABLE employee	
	([id] int, [name] varchar(7))
;
	
INSERT INTO employee	
	([id], [name])
VALUES
	(1, 'fred'),
	(2, 'barney'),
	(3, 'bam bam')
;

CREATE TABLE spouse	
	([id] int, [emp_id] int, [spouse_name] varchar(7), [is_current] smallint)
;
	
INSERT INTO spouse	
	([id], [emp_id], [spouse_name], [is_current])
VALUES
	(1, 1, 'pauline', 0),
	(2, 1, 'wilma', 1),
	(3, 2, 'betty', 1)
;

Open in new window

So here are 2 queries the first one is correct, it uses a left join to spouse but at the same time only looks for [is_current]=1 so former wives are not listed. The second query is wrong because unmarried employees are not listed even though it uses a left join.
**Query 1**:

SELECT
  e.*
, s.*
FROM employee AS e
LEFT JOIN spouse AS s ON e.id = s.emp_id AND s.is_current = 1


**[Results][1]**:

| ID |    NAME | EMP_ID | SPOUSE_NAME | IS_CURRENT |
|----|---------|--------|-------------|------------|
|  1 |    fred |      1 |       wilma |          1 |
|  2 |  barney |      2 |       betty |          1 |
|  3 | bam bam | (null) |      (null) |     (null) |


**Query 2**:

SELECT
  e.*
, s.*
FROM employee AS e
LEFT JOIN spouse AS s ON e.id = s.emp_id
WHERE s.is_current = 1


**[Results][2]**:

| ID |   NAME | EMP_ID | SPOUSE_NAME | IS_CURRENT |
|----|--------|--------|-------------|------------|
|  1 |   fred |      1 |       wilma |          1 |
|  2 | barney |      2 |       betty |          1 |

Open in new window

So, the effect of using is_current = 1 in the where clause is that unmarried employees are no longer listed (which is the same as using an inner join between the tables).  

Filtering by a constant in the left join produced the correct result.

You can run the queries yourself here: http://sqlfiddle.com/#!3/8bd9f/1

btw: You could fix the send query by adding "OR is_current IS NULL" so you can use a where clause to produce the wanted result - it's just easier filtering within the join.

Author

Commented:
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
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>" 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

Author

Commented:
Ah - I see.  Thanks for all your help!!

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial