Join two tables in sql with different rows

Question

I have two sql queries that give me two tables with a different number of rows, I need to combine them - just mutually display the two tables together

Table 1

row1 row2 ============== 12 gfd 13 jf 14 gfd 15 jhhh 

table 2

  row3 row4 ============== 18 gjkd 11 jfyty 

Result

 row1 row2 row3 row4 ====================================== 12 gfd 18 gjkd 13 jf 11 jfyty 14 gfd 15 jhhh 

The data in each table is completely different and have different field names. I am using Oracle DB, the query should be used in iReport.

Answer 1

Use LEFT OUTER JOIN

 select * from table1 t1 LEFT OUTER join table2 t2 on t1.row1=t2.row3 

SQL script demo

EDIT1:

This works in MySQL.

This is for your updated Question

SQL Fiddle new demo

 select row1,row2,row3,row4 from (select row1,row2,@i: =@i +1 AS rn from Table1,(SELECT @i:=0) r)t1 left join (select row3,row4,@j: =@j +1 AS rn from Table2,(SELECT @j:=0) r )t2 on t1.rn=t2.rn 

Answer 2

It seems to me that you want to combine two tables in the order of the rows in each table. I'm sorry, but SQL has no concept of implicit row order inside a table, and there is no way in SQL to concatenate columns from two (or more) tables without any column to allow JOIN.

The closest things you can do in SQL:

• CROSS JOIN between two tables. This would repeat every row from table1 to every row of table2:

 SELECT t1.row1, t1.row2, t2.row3, t2.row4 FROM table1 t1 CROSS JOIN table1 t2 -- row1 row2 row3 row4 -- ---- ------ ---- ------ -- 12 gfd 18 gjkd -- 13 jf 18 gjkd -- 14 gfd 18 gjkd -- 15 jhhh 18 gjkd -- 12 gfd 11 jfyty -- 13 jf 11 jfyty -- 14 gfd 11 jfyty -- 15 jhhh 11 jfyty 

• Create another (auxiliary) column and keep the "order" of your rows in each table (there are many ways to do this using cursors, sequences, ...). Then use these columns to build the OUTER JOIN :

 SELECT t1.auxOrder1, t1.row1, t1.row2, t2.auxOrder2, t2.row3, t2.row4 FROM table1 t1 LEFT OUTER JOIN table1 t2 ON (t1.auxOrder1 = t2.auxOrder2) -- auxOrder1 row1 row2 auxOrder2 row3 row4 -- --------- ---- ------ --------- ---- ------ -- 1 12 gfd 1 18 gjkd -- 2 13 jf 2 11 jfyty -- 3 14 gfd -- 4 15 jhhh 

By the way, why did you create columns with names such as "row1", "row2"? If it is not intended, I think it can be confusing.

Answer 3

Use LEFT JOIN to do this so that it returns all the records in the LEFT table, even if it does not match another table.

 SELECT a.*, b.* FROM table1 a LEFT JOIN table2 b ON a.row1 = b.row3 

SQLFiddle Demo

For more information: Visual representation of associations

UPDATE 1

If you are using SQL Server

 SELECT a.row1, a.row2, b.row3, b.row4 FROM ( SELECT ROW_NUMBER() OVER (ORDER BY row1 ASC) rn, row1, row2 FROM table1 ) a LEFT JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY row3 ASC) rn, row3, row4 FROM table2 ) b ON a.rn = b.rn 

SQLFiddle Demo

Answer 4

To display unique values ​​for the name and address from two tables, rather than repeating the values ​​when performing a join, this query will take unique values ​​from both tables and display them in order.

This is what I was able to do using oracle 11:

 select name, address, table1_id, table2_id from ( select table1_id, name, table_id||(row_number () over (partition by table_id order by name desc nulls last)) as table1_id_row from ( select distinct table_id table1_id, name from table1 where table_id is not null ) ) FULL OUTER JOIN ( select table2_id, address, table_id||(row_number () over (partition by table_id order by address desc nulls last)) as table2_id_row from ( select distinct table_id table2_id, address, city, state, zip from table2 where table_id is not null ) ) ON table1_id_row = table2_id_row where table1_id = '123456789' or table2_id = '123456789'