Geek asks and answers

Alternative to UNION's suggestion in Mysql

I have two tables: - table a, table b.

table a

--- --- ID

 1
 2
 3
 4
 5
 7

table b

--- ID ----

 2
 3
 4
 5
 6

I need to get Output Like this without UNION . Team: -

---- ID -----

1
2
3
4
5
6
7

Note. I have one solution with combining: -

  **select * from a
      UNION
   select * from b;**

I need an alternative to this. according to experts.

+3
source share
6 answers

We need another table with (at least) 2 rows for this:

CREATE TABLE d
  ( id INT NOT NULL 
  );

INSERT INTO d
  (id)
VALUES
  (0), (1) ;

Then, if we want to have only one request, we can use ( this is for fun, DO NOT USE in production , therefore we have ): UNION

SELECT DISTINCT
    COALESCE(aa.id, bb.id) AS id
FROM 
    d
  LEFT JOIN a AS aa ON d.id = 0
  LEFT JOIN b AS bb ON d.id = 1
WHERE 
    COALESCE(aa.id, bb.id) IS NOT NULL
ORDER BY 
    id ;

Tested with SQLfiddle.com , and for other table combinations:

1 - 1
2 - 2
0 - 1
0 - 2
0 - 0

+5

:

, MS-SQL, , MySQL, MYSql ! .

    SELECT (
      CASE
        WHEN b.ID IS NULL
        THEN a.ID
        WHEN b.ID=a.ID
        THEN b.ID
        ELSE b.ID
     END)
    FROM
      (SELECT ID FROM table2
      )b
    FULL OUTER JOIN
      (SELECT ID FROM table1
      ) a
   ON a.ID=b.ID

Fiddle: http://sqlfiddle.com/#!3/c657d/13

MYSQL:

SELECT DISTINCT COALESCE(t1.id, t2.id) id


 FROM 

(
  SELECT TABLE_NAME <> 'table_a' n
    FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = SCHEMA()
     AND TABLE_NAME IN('table_a', 'table_b')
) t LEFT JOIN table_a t1 
    ON t.n = 0 LEFT JOIN table_b t2
    ON t.n = 1

: http://sqlfiddle.com/#!2/c657d8/34

+4

, UNION, :

CREATE TABLE temp_ids(ID INT);

INSERT INTO temp_ids SELECT ID FROM a;
INSERT INTO temp_ids SELECT ID FROM b;

SELECT DISTINCT ID FROM temp_ids;
+1

NULL.

0

As an abstract exercise (if this is an interview question, we expect returns!) One ugly, ineffective solution would be to create a Cartesian product and filter unique values:

SELECT DISTINCT IF(a<>b, b.id, a.id) 
FROM a, b
ORDER BY 1
;
0
source

Use FULL OUTER JOINfor example:

SELECT CASE 
         WHEN t1.id IS NULL THEN t2.id 
         ELSE t1.id 
       END AS id 
FROM   t1 
       FULL OUTER JOIN t2 
                    ON t1.id = t2.id 
ORDER  BY id

Note. Mysql does not support full outer join.

Working demo: http://sqlfiddle.com/#!6/b7684/10

0
source

More articles:


All Articles