Web Development

regular (inner join) in crystal
then have it show only the records where 'isnul' on the first table.

I know you actually should just change the join type. But I like to see what records are present for auditing purposes. It would of course return records faster if done on server by changing join type

SELECT
Table_1.COLUMN_1
FROM
Table_1 LEFT OUTER JOIN Table_2
ON
Table_1.COLUMN_1 = Table_2.COLUMN_2
WHERE
Table_2.COLUMN_2 IS NULL
GROUP BY
Table_1.COLUMN_1

I would use the IN() subquery to exclude records, it is simpler to read the TSQL. However both seem to cost the same CPU time.
Be careful with large tables.


-- create temp tables
DECLARE @t1 TABLE (col_a NCHAR)
DECLARE @t2 TABLE (col_b NCHAR)

INSERT INTO @T1 (col_a) VALUES ('A')
INSERT INTO @T1 (col_a) VALUES ('A')
INSERT INTO @T1 (col_a) VALUES ('A')
INSERT INTO @T1 (col_a) VALUES ('B')
INSERT INTO @T1 (col_a) VALUES ('B')
INSERT INTO @T1 (col_a) VALUES ('C')
INSERT INTO @T1 (col_a) VALUES ('C')
INSERT INTO @T1 (col_a) VALUES ('C')
INSERT INTO @T1 (col_a) VALUES ('D')
INSERT INTO @T1 (col_a) VALUES ('E')
INSERT INTO @T1 (col_a) VALUES ('E')
INSERT INTO @T1 (col_a) VALUES ('E')
INSERT INTO @T1 (col_a) VALUES ('E')
INSERT INTO @T1 (col_a) VALUES ('F')
INSERT INTO @T1 (col_a) VALUES ('F')
INSERT INTO @T1 (col_a) VALUES ('G')
INSERT INTO @T1 (col_a) VALUES ('G')
INSERT INTO @T1 (col_a) VALUES ('G')

INSERT INTO @T2 (col_b) VALUES ('A')
INSERT INTO @T2 (col_b) VALUES ('B')

--simple version to read
SELECT DISTINCT col_a FROM @t1 WHERE col_a NOT IN (SELECT col_b FROM @t2)

-- more complex
SELECT DISTINCT col_a FROM @t1 LEFT OUTER JOIN @t2 ON col_a = col_b WHERE col_b IS NULL

SELECT DISTINCT(Column_1) from Table_1 where
NAME NOT IN (SELECT Column_2 FROM Table_2)

select distinct column_1 from testtable_1 t1
where column_1 not in (select column_2 from testtable_2)

SELECT Column_1 FROM Table_1
WHERE Column_1 NOT IN
(SELECT Column_2 FROM Table_2)
GROUP BY Column_1

1st:

select Column_1
from Table_1 left join Table_2
on Column_1 = Column_2
where Column_2 is null

2nd:

select Column_1
from Table_1
where Column_1 not in
(select Column_2 from Table_2)

Both queries will have the same output:

Column_1
--------
C
C
C
D
E
E
E
E
F
F
G
G
G

If you dont want to have repeated entries, just use the "distinct" statement at the beginning of the query.