I have two tables like this:

select audi_model from audi

select tesla_model from tesla

How can I join them to have one table like this

CodePudding user response：

This should work:

SELECT audi_model
     , tesla_model
  FROM (SELECT audi_model, ROWNUM AS rid
          FROM audi) audi
     , (SELECT tesla_model, ROWNUM AS rid
          FROM tesla) tesla
 WHERE audi.rid( ) = tesla.rid;

CodePudding user response：

This transformation is typically done in the middle-tier or client application rather than in a database query.

You can give the tables each a unique sequential index and then FULL OUTER JOIN on that index:

SELECT audi_model,
       tesla_model
FROM   (SELECT audi_model,
               ROWNUM AS rn
        FROM   audi) a
       FULL OUTER JOIN
       (SELECT tesla_model,
               ROWNUM AS rn
        FROM   tesla) t
       ON (a.rn = t.rn)

Which, for the sample data:

CREATE TABLE audi (audi_model) AS
SELECT 'model_1' FROM DUAL UNION ALL
SELECT 'model_2' FROM DUAL UNION ALL
SELECT 'model_3' FROM DUAL UNION ALL
SELECT 'model_4' FROM DUAL UNION ALL
SELECT 'model_5' FROM DUAL;

CREATE TABLE tesla (tesla_model) AS
SELECT 't_model_1' FROM DUAL UNION ALL
SELECT 't_model_2' FROM DUAL UNION ALL
SELECT 't_model_3' FROM DUAL UNION ALL
SELECT 't_model_4' FROM DUAL;

Outputs:

AUDI_MODEL	TESLA_MODEL
model_1	t_model_1
model_2	t_model_2
model_3	t_model_3
model_4	t_model_4
model_5	null

If you then:

INSERT INTO tesla(tesla_model)
SELECT 't_model_5' FROM DUAL UNION ALL
SELECT 't_model_6' FROM DUAL;

And run it again, the output is:

AUDI_MODEL	TESLA_MODEL
model_1	t_model_1
model_2	t_model_2
model_3	t_model_3
model_4	t_model_4
model_5	t_model_5
null	t_model_6

db<>fiddle here

As an aside, you may be better to represent your data as single models table with model and brand columns.