selecting result from two tables in form clause

Question

Hi i have two table A and B.A has 6 rows and b has 7 rows.Both tables have common value in name column.All the 6 rows of a table is present in b table on name column.

When i write query select * from a,b where a.name = b.name i get 14 rows returned i was expecting an inner join of with 6 rows in result.

Please explain me how query works when we have two tables in form clause.

Table A

Table B

query is select * from a,b where a.tt = b.tt and a.nename=b.nename;

reuslt is

I can but i am just asking what happens when we use such query,Is it a full joining happening or what is going on here ? — Sunil Kumar

Conrad Frix Conrad Frix · Accepted Answer · 2017-03-15T17:56:04

You've got duplicates in both tables (except for {2, 2017-03-04 03:00:00} which has three copies) which is why you get 14 = (2 * 4) + (2 * 3).

It's very hard to make sense of duplicate data. It's even harder to do when it duplicated on both sides of a join.

You could do something like

With fixedA (SELECT 
               *, 
               row_number() over (partition by nename, tt order by nename) rn 
             FROM 
                A),
     fixedb (SELECT 
               *, 
               row_number() over (partition by nename, tt order by nename) rn 
             FROM 
                B)
      SELECT * 
      FROM fixedA a  full outer join  fixedb  b
           on a.neName = b.neName
             and a.tt = b.tt
             and a.rn = b.rn

This will however leave one B record with a Null A record

The row_number also seems to do what cellID does so you could just do

SELECT * 
          FROM a  full outer join  b
               on a.neName = b.neName
                 and a.tt = b.tt
                 and a.cellID = b.cellID

selecting result from two tables in form clause

2 Answers