1

I have two tables. tl and t2. Each table has the columns: ID, name. The ID is a PK and Auto increment. The two tables are not connected to each other. The t2.name is a subset of t1.name, but after adding www. at the beginning of the string in the name. Ex: 

`t1`.`name`= ggg
`t2`.`name`= www.ggg

What I am trying to do is: selecting all the record of where t1.name not inserted in t2. So I wrote the following query:

select concat('www.',name) 
from db.t1
LEFT JOIN db.t2 
ON t2.name = t1.name
WHERE NOT EXISTS (select name from db.t2);

But, I get 0 result. I am certain that there are records that are available in t1 but not in t2. How can I select all the names in t1 that are not in t2??

John Woo
  • 258,903
  • 69
  • 498
  • 492
user1810868
  • 1,565
  • 8
  • 23
  • 30
  • 1
    `WHERE NOT EXISTS (SELECT name FROM db.t2)` will cause the query to return zero or all results depending on if the table t2 contains any or no rows since there is no condition on it. – gapple Nov 09 '12 at 00:18

3 Answers3

1
select concat('www.',name) 
from db.t1
LEFT JOIN db.t2 ON t2.name = concat('www.', t1.name)
WHERE db.t2.name IS NULL
juergen d
  • 201,996
  • 37
  • 293
  • 362
0
SELECT CONCAT('www.', tbl1.name)
FROM db.t1 AS tbl1
WHERE NOT EXISTS (
    SELECT 1
    FROM db.t2 AT tbl2
    WHERE CONCAT('www.', tbl1.`name`) = tbl2.`name`
)
GunnerL3510
  • 715
  • 3
  • 15
  • 1) I can not figure out what do you mean by `select 1` in your second select. 2) When I assume that is t2.name It did not work. It return all the records in t1. – user1810868 Nov 09 '12 at 00:28
  • In an exists tests all that matters is that there is, or is not, a result set. By returning the value 1 instead of a record you decrease the amount of work to be done. – GunnerL3510 Nov 09 '12 at 00:29
  • Not sure why this would return all of the records in t1. It is explicitly stating to only return those records from t1 where there isn't a record in t2 that the name in t1, after adding www. to the beginning of it, matches a name in t2. In the majority of cases, an EXISTS test is more performant than doing a JOIN and testing for NULL. Only testing will confirm either way though. – GunnerL3510 Nov 09 '12 at 00:36
0

This should do: 

select t.name FROM
(
 select concat('www.',t1.name) AS name
 from t1
) t
LEFT JOIN t2
ON t2.name = t.name
WHERE t2.name IS NULL
nawfal
  • 70,104
  • 56
  • 326
  • 368