How can I join two tables but only return rows that don't match?
Solution 1
SELECT T1.*
FROM T1
WHERE NOT EXISTS(SELECT NULL
FROM T2
WHERE T1.ID = T2.ID
AND T1.Date = T2.Date
AND T1.Hour = T2.Hour)
It could also be done with a LEFT JOIN
:
SELECT T1.*
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
AND T1.Date = T2.Date
AND T1.Hour = T2.Hour
WHERE T2.ID IS NULL
Solution 2
Use a LEFT JOIN
and filter out the lines that have non-NULL
T2 columns:
SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID
AND T1.Date = T2.Date AND T1.Hour = T2.Hour
WHERE T2.ID IS NULL
Breakthrough
I'm currently working for Eagle Vision Systems, where we specialize in using computer vision and machine learning to solve motion control problems for industrial robotics. I hold a B.E.Sc in Electrical Engineering, with special focus on parallel/hardware implementations of computer vision algorithms for real-time systems/robots. In my studies, I focused mostly on computer architectures, embedded systems, image processing, and parallel processing/GPGPU. For now, I'm just enjoying changing the world one robot at a time,, updating my website when I can, working on various projects (like PySceneDetect), slaving away on my thesis/research, and of course, contributing to Stack Exchange! If you should need to contact me directly, leave a comment on this page and I will e-mail you back. (Extensive) Experienced with Linux / Windows, assembly (x86 & m68k), C/C++, Python, VBA, C#, MATLAB, SDL/SDL2, GTK+2/3 + Glade, Qt, OpenGL, OpenCV, CUDA, SQL, SQLAlchemy, OpenCL, and VHDL / Verilog.
Updated on May 02, 2020Comments
-
Breakthrough about 4 years
I have two tables which look like this:
T1: ID | Date | Hour | Interval T2: ID | Date | Hour
I basically need to join these tables when their IDs, dates, and hours match. However, I only want to return the results from table 1 that do not match up with the results in table 2.
I know this seems simple, but where I'm stuck is the fact that there are multiple rows in table 1 that match up with table 2 (there are multiple intervals for any given hour). I need to return all of these intervals so long as they do not fall within the same hour period in table 2.
Example data:
T1: 1 | 1/1/2011 | 1 | 1 1 | 1/1/2011 | 1 | 2 1 | 1/1/2011 | 2 | 1 1 | 1/1/2011 | 2 | 2 T2: 1 | 1/1/2011 | 1
My expected result set for this would be the last two rows from
T1
. Can anyone point me on the right track?