Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Help with Corelated sub-query

pshah2k
Member Posts: 172
I am trying to improve this query? Is there a better way to write the query and get the same results. I hate the fact that I am querying a table 3 times.
Select statement
Select statement
WITH t1 AS (SELECT 101 AS ID , 1 AS a , NULL AS b , 5 AS c FROM DUAL UNION ALL SELECT 102 , NULL , 2 , 5 FROM DUAL UNION ALL SELECT 101 , 3 , 5 , 7 FROM DUAL UNION ALL SELECT 101 , 1 , NULL , NULL FROM DUAL) , t2 AS (SELECT 101 AS ID , 1 AS x FROM DUAL UNION ALL SELECT 101 , 5 FROM DUAL UNION ALL SELECT 102 , 5 FROM DUAL UNION ALL SELECT 102 , 2 FROM DUAL) SELECT * FROM t1 WHERE t1.ID = 101 AND ( t1.a IS NULL OR t1.a IN (SELECT t2.x FROM t2 WHERE t2.ID = t1.ID)) AND ( t1.b IS NULL OR t1.b IN (SELECT t2.x FROM t2 WHERE t2.ID = t1.ID)) AND ( t1.c IS NULL OR t1.c IN (SELECT t2.x FROM t2 WHERE t2.ID = t1.ID))Output:
ID A B C ---------- ---------- ---------- ---------- 101 1 5 101 1-- Thanks
Comments
-
Please, try this:
WITH t1 AS
(SELECT 101 AS ID
, 1 AS a
, NULL AS b
, 5 AS c
FROM DUAL
UNION ALL
SELECT 102
, NULL
, 2
, 5
FROM DUAL
UNION ALL
SELECT 101
, 3
, 5
, 7
FROM DUAL
UNION ALL
SELECT 101
, 1
, NULL
, NULL
FROM DUAL)
, t2 AS
(SELECT 101 AS ID
, 1 AS x
FROM DUAL
UNION ALL
SELECT 101
, 5
FROM DUAL
UNION ALL
SELECT 102
, 5
FROM DUAL
UNION ALL
SELECT 102
, 2
FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND EXISTS
(SELECT *
FROM t2
WHERE t1.ID = t2.ID
AND t1.a = t2.x OR t1.a IS NULL
AND t1.b = t2.x OR t1.b IS NULL
AND t1.c = t2.x OR t1.c IS NULL);
Regards,
Luis -
Hi lmescher.
Unfortunately, I think that your query is wrong.
for instanceWITH t1 AS
(SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
SELECT 101, 5,99,99 FROM DUAL UNION ALL
SELECT 101, 1, NULL, NULL FROM DUAL)
, t2 AS
(SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 101 , 3 FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 101 , 7 FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND EXISTS
(SELECT *
FROM t2
WHERE t1.ID = t2.ID
AND t1.ColA = t2.ColX OR t1.ColA IS NULL
AND t1.ColB = t2.ColX OR t1.ColB IS NULL
AND t1.ColC = t2.ColX OR t1.ColC IS NULL);ID ColA ColB ColC
If you has mistake for combination of "and" and "or",
--- ---- ---- ----
101 1 null 5
101 3 5 7
101 5 99 99
101 1 null null
Query becomes below.
But ResultSet remains wrong ResultSet.WITH t1 AS
(SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
SELECT 101, 5,99,99 FROM DUAL UNION ALL
SELECT 101, 1, NULL, NULL FROM DUAL)
, t2 AS
(SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 101 , 3 FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 101 , 7 FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND EXISTS
(SELECT *
FROM t2
WHERE t1.ID = t2.ID
AND (t1.ColA = t2.ColX OR t1.ColA IS NULL)
AND (t1.ColB = t2.ColX OR t1.ColB IS NULL)
AND (t1.ColC = t2.ColX OR t1.ColC IS NULL));ID ColA ColB ColC
--- ---- ---- ----
101 1 null null -
This is an interesting question.
WITH t1 AS
(SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
SELECT 102, NULL, 2, 5 FROM DUAL UNION ALL
SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
SELECT 101, 5,99,99 FROM DUAL UNION ALL
SELECT 101, 1, NULL, NULL FROM DUAL)
, t2 AS
(SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 102 , 5 FROM DUAL UNION ALL
SELECT 102 , 2 FROM DUAL UNION ALL
SELECT 101 , 3 FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 101 , 7 FROM DUAL)
select *
from t1 a
where ID = 101
and exists(select 1
from t2 b
where b.ID = a.ID
having (a.ColA is null or max(case when b.ColX = a.ColA then 1 else 0 end) = 1)
and (a.ColB is null or max(case when b.ColX = a.ColB then 1 else 0 end) = 1)
and (a.ColC is null or max(case when b.ColX = a.ColC then 1 else 0 end) = 1));ID ColA ColB ColC
Furthermore,
--- ---- ---- ----
101 1 null 5
101 3 5 7
101 1 null null
We can use below alternative solution.
Below alternative solution is used Boolean arithmetic (http://www.allaboutcircuits.com/vol_4/chpt_7/2.html)WITH t1 AS
This thread is dealt logic which is "max(case when P(X) then 1 else 0 end) = 1" is for some X:P(X).
(SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
SELECT 102, NULL, 2, 5 FROM DUAL UNION ALL
SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
SELECT 101, 5,99,99 FROM DUAL UNION ALL
SELECT 101, 1, NULL, NULL FROM DUAL)
, t2 AS
(SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 102 , 5 FROM DUAL UNION ALL
SELECT 102 , 2 FROM DUAL UNION ALL
SELECT 101 , 3 FROM DUAL UNION ALL
SELECT 101 , 5 FROM DUAL UNION ALL
SELECT 101 , 7 FROM DUAL)
select *
from t1 a
where ID = 101
and exists(select 1
from t2 b
where b.ID = a.ID
having max(case when b.ColX = a.ColA or a.ColA is null then 1 else 0 end)
* max(case when b.ColX = a.ColB or a.ColB is null then 1 else 0 end)
* max(case when b.ColX = a.ColC or a.ColC is null then 1 else 0 end) = 1);
And this thread deals alike question.
2067650
I mentioned alike logic in this thread.
2040085
I recommend these articles.
http://www.dbazine.com/ofinterest/oi-articles/celko5
http://www.dbazine.com/ofinterest/oi-articles/celko18 -
Aketi,
I stand corrected.
Regards,
Luis -
Thanks a lot for your solution. It works like a charm. I can now add one more thing that I should try to leran.
I also want to thank others that responded.
This discussion has been closed.