MYSQL want to compare two table and select data

Question

I have two tables.

table 1
1
2
3
4

table 2
1
2
3

i want to select data from tables

table 1 have all data. but table 2 may have all data or not. so if table 1 data exits in table two data select table 2 data otherwise select table one data.

if table 1 data not exit table 2 select table 1 data.

how can do that?

Answer 1

You need OUTER JOIN:

SELECT t1.ID, 
       COALESCE(t2.col1, t1.col1) AS col1,  -- prefer data from table_2 if exists
       COALESCE(t2.col2, t1.col2) AS col2,
       -- ...
FROM table_1 t1                  -- "table 1 have all data"
LEFT JOIN table_2 t2             -- "table 2 may have all data or not"
  ON t1.ID = t2.ID;              

Answer 2

Try FULL OUTER JOIN

SELECT t2.col1, t1.col1
FROM 
    table_1 t1 FULL OUTER JOIN table_2 t2
    ON t2.col1 = t1.col1

Or, if full joins are not supported by your database, try emulating them.