How to use SQL UNION

Question

well i do not know how to use sql union properly . i have two tables with domains(url) i want to compare table1 with table2 so that i get unique domain from table 2 which is not in table 1.

example

table1

id   domain
1    google.com
2    facebook
3    stackoverflow.com

table2

id   domain
1    youtube.com
2    facebook
3    stackoverflow.com

so i want that i compare both tables and get youtube.com because its not in table1 i used sql union like that

$query="SELECT table1.domain, table2.domain "."FROM table1, table2 "."WHERE table1.domain != table2.domain";
$result = mysql_query($query) or die(mysql_error());
while ($rec = mysql_fetch_array($result))
{
$new_url=$rec["domain"];
}

now i get double results like youtube.com youtube.com so help whats wrong with my sql statement

You need to use a JOIN, not a UNION – dtech Apr 11 '12 at 09:33 — dtech, Apr 11 '12 at 09:33
Why do you use the . separator in the string? – Dion Apr 11 '12 at 09:37 — Dion, Apr 11 '12 at 09:37

score 0 · Accepted Answer · answered Apr 11 '12 at 09:34

0

You should use

SELECT t2.domain FROM
table2 t2 LEFT JOIN table1 t1
    ON t2.domain = t1.domain
WHERE t1.domain IS NULL

answered Apr 11 '12 at 09:34

Marco

56,740
14
129
152

DonCallisto · Answer 2 · 2012-04-11T09:41:30.580

0

This is the SQL that you're looking for

SELECT DISTINCT t1.domain FROM t1
WHERE domain not in (SELECT t2.domain FROM t2)

edited Apr 11 '12 at 09:41

answered Apr 11 '12 at 09:35

DonCallisto

29,419
9
72
100

See Marco's answer, it is far faster on most DBMS's – dtech Apr 11 '12 at 09:39
@dtech Yes, I know. I only provide a different kind of solution... Read it like "logic training" :) – DonCallisto Apr 11 '12 at 09:40

score 0 · Answer 3 · edited May 23 '17 at 10:26

0

The relational operator you require is difference (union is analogous to addition). In standard SQL the keyword is EXCEPT (in Oracle it is MINUS, which is a little more intuitive IMO):

SELECT domain
  FROM table2
EXCEPT 
SELECT domain
  FROM table1

Does the 'php' tag imply MySQL? MySQL lacks an explicit relational difference operator, so you would need to use an antijoin construct.

edited May 23 '17 at 10:26

Community

1
1

answered Apr 11 '12 at 10:47

onedaywhen

55,269
12
100
138

How to use SQL UNION

3 Answers3