CODEWITHSUNDEEP
sqloracle-databasehibernatehql
Ashok Goli
Ashok Goli

Reputation: 5183

Fastest SQL & HQL Query for two tables

Table1: Columns A, B, C Table2: Columns A, B, C

Table 2 is a copy of Table 1 with different data. Assume all columns to be varchar

Looking for a single efficient query which can fetch:

Any differences between the Oracle SQL & HQL for the same query will be appreciated.

I'm fiddling with Joins, Unions & Minus but not able to get the correct combination.

Upvotes: 1

Views: 1113

Answers (2)

Ronnis
Ronnis

Reputation: 12843

This query returns all rows in table1, plus all rows in table2 which does not exist in table1, given that column a is the common key.

select a,b,c
  from table1
 union 
   all
select a,b,c
  from table2
 where a not in(select a from table1);

There may be different options available depending on the relative sizes of table1 and table2 and the expected overlap.

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15399

SQL:

SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2 T2
WHERE NOT EXISTS(
    SELECT 'X' FROM Table1 T1
    WHERE T1.A = T2.A
)

HQL:

You must execute two different query an discard the element by Table2 result in a Java loop because in HQL doesn't exist UNION command.

Alternatatively you can write the first query for Table1 and the second query must have a not in clause to discard Table1 A field.

Solution 1:

Query 1:

SELECT * FROM Table1

Query 2: SELECT * FROM Table2

and then you apply a discard loop in Java code

Solution 2:

Query 1:

SELECT * FROM Table1

Query 2:

SELECT * FROM Table2 WHERE Table2.A not in (SELECT Table1.A from Table1)

Upvotes: 1

Related Questions