Return fields from query and subquery

Question

I'd like join 2 tables but without a INNER JOIN but with nested query.

I have 2 tables :

TableA : AField1, AField2, AField3, AField4
TableB : BField1, BField2, BField3

The link between table are AField1 and BField1.

SELECT AField1, AField2, AField3
 FROM TableA
 WHERE AField1 IN (
    SELECT BField1 FROM TableB
    WHERE BField3 like 'E11%'
    );

I'd like in the result AField1, AField2, AField3, AField4 and BField2, BField3

Any idea ?

Thanks

So what you want is: You have table1 and table2 and you are trying to get data of table2 without selecting from table2? Why do you want to achieve this without an INNER JOIN? — Himanshu, Sep 25 '13 at 05:50
Can you explain what exactly you want to achieve with some sample data? — Himanshu, Sep 25 '13 at 05:54
It's clear, in the result I need some field from TableA and TableB. Why not INNER JOIN, I have performance trouble and I'd like test another way. — TheBoubou, Sep 25 '13 at 06:04

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

I believe this is what you're trying to achieve--obviously, this solution is contingent on the join integrity. So you'll have to makes sure the PK match to ensure you're not getting an improper join.

SELECT a.AField1,
  a.AField2,
  a.AField3,
  bb.BField2,
  bb.BField3
FROM TableA a
WHERE EXISTS
  (SELECT 'X'
  FROM TableB b
  WHERE b.BField3 LIKE 'E11%'
  AND a.AField1 = b.BField1
  )
  (SELECT * FROM TableB WHERE BField3 LIKE 'E11%'
  ) bb
AND a.AField1 = bb.BField1

In SQL you can alias the tables--read more about it here:When to use SQL Table Alias

edited May 23 '17 at 11:50

Community

1
1

answered Sep 25 '13 at 06:09

Roberto Navarro

948
4
16

Since the OP is trying to improve the performance, I don't think this will perform better than `JOIN`. – Himanshu Sep 25 '13 at 06:14
You could use a correlated subquery; however, you would loose the ability to select b.BField2, b.BField3 – Roberto Navarro Sep 25 '13 at 06:15
[I told him the same](http://stackoverflow.com/questions/18997150/return-fields-from-query-and-subquery#comment-28064609) – Himanshu Sep 25 '13 at 06:17
Ok--I updated my code to include a correlated subquery--but then re-select the table to only use tableB to get the column names. I don't have a working DB to test this with, but I'd be tempted to look at the explain plan on these. – Roberto Navarro Sep 25 '13 at 06:18
Is that the column name? I took the column name from your example--so there's now way for me to actually validate. – Roberto Navarro Sep 25 '13 at 06:44
I get this error : "Toker was not valid. Valid Tokens: FOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE". – TheBoubou Sep 25 '13 at 10:53

Return fields from query and subquery

1 Answers1