How do you exclude another table from a query in SQL Server?

Let's assume you have the following two SQL Server tables:

t1:

|----------------------------------------------|
|name | day_planed | day_canceled | discription|
|----------------------------------------------|
|alpha| 2015-11-02 | 2015-11-01   | some       |
|alpha| 2015-11-02 | 2015-10-30   | text       |
|beta | 2015-11-02 | 2015-11-01   | here       |
|----------------------------------------------|

t2:

|----------------------------------------------|
|name | day_planed | day_canceled | discription|
|----------------------------------------------|
|alpha| 2015-11-02 | 2015-10-30   | text       |
|----------------------------------------------|

In an query of t1 I now want every entry except those of t2. I already tried something similar to

SELECT * 
FROM t1 AS A 
LEFT JOIN t2 as B ON (A.name = B.name 
                  AND A.day_planed = B.day_planed 
                  AND A.day_canceled != B.day_canceled)

Unfortunately I don't get it why it does not exclude the row from t2 in the query of t1.

A second question would be, if there is actual an easy way to make a query of t1 without t2 by just returning the row with the maximum description. I tried looking into it in SQL Server, but could only find the first identifier, which does not work for this "wonderful" implementation of sql...

Solution

There are a few ways to do it in this "wonderful" implementation.

SELECT * FROM t1
EXCEPT
SELECT * FROM t2

is one. Another is:

SELECT * 
FROM t1
WHERE NOT EXISTS
    ( SELECT *
      FROM t2
      WHERE t2.name = t1.name
      AND t2.day_planed = t1.day_planed
      AND t2.day_canceled = t1.day_canceled
  )

Or you could use LEFT JOIN and check for rows that didn't match with WHERE t2.name IS NULL after your ON clause. Like what you have but with = instead of !=

SELECT t1.* 
FROM t1
LEFT JOIN t2
      ON t2.name = t1.name
      AND t2.day_planed = t1.day_planed
      AND t2.day_canceled = t1.day_canceled
WHERE t2.name IS NULL;

If you want to check every column (including discription), go with EXCEPT.