I have two differents tables. The have some columns in common, for this example lets say 'name' and 'id'.
( SELECT name,id FROM table1
SELECT name,id FROM table2)
( SELECT name,id FROM table2
SELECT name,id FROM table1)
After lots of comments I think this is what you're after...
SELECT T1.* FROM table1 t1 LEFT JOIN table2 t2 on T1.ID = T2.ID and T1.Name = T2.Name AND E2.event_Time_UTC between convert(datetime,'2016-02-09 00:00:20',101) and convert(datetime '2016-02-09 23:59:52',101) WHERE T2.Name is null AND E1.Event_Time_UTC between convert(datetime,'2016-02-09 00:00:20',101) and convert(datetime,'2016-02-09 23:59:52',101)
you may allow implicit casting to work but above is the explicit approach...
if not then you would need to cast the string dates to a date time, assuming Event_Time_UTC is a date/time datatype.
a left join lets us return all records from the 1st table and only those that match from the 2nd.
the t1.* returns only the columns from table1. The join critiera (on) allows us to identify those records which match so they can then be eliminated in the where cluase by 'where t2.name is null' they will always be null when no record match in t2.
Thus you get a result set that is: all records from t1 without a matching record on name and id in table2.
----------CONTENT BELOW NO LONGER RELEVANT BASED ON COMMENTS------
I Redacted previous answer alot because using sql server not mySQL and I know you want multiple records not table1 and table2 joined.
In the below I create two tables: table1 and table2. I then populate table1 and table2 with some sample data
I then show how to get only those records which exist in one table but not the other; returning a separate ROW for each. I then go into detail as to why I choose this approach vs others. I'll finally review what you've tried and try to explain why I don't think it will work...
create table table1 ( ID int, name varchar(20), col1 varchar(20), col2 varchar(20), col3 varchar(20)); Create table table2 ( id int, name varchar(20)); Insert into table1 values (1,'John','col1','col2','col3'); Insert into table1 values (2,'Paul','col1','col2','col3'); Insert into table1 values (3,'George','col1','col2','col3'); Insert into table2 values (1,'John'); Insert into table2 values (4,'Ringo');
SELECT T1.name, T1.ID, T1.Col1, T1.Col2, T1.Col3 FROM Table1 T1 LEFT JOIN Table2 T2 on T1.Name = T2.Name and T1.ID = T2.ID WHERE T2.ID is null UNION ALL SELECT T2.name, T2.ID, NULL, NULL, NULL FROM Table1 T1 RIGHT JOIN Table2 T2 on T1.Name = T2.Name and T1.ID = T2.ID WHERE T1.ID is null ;
which results in...
Notice John isn't there as it's in both tables. We have the other 2 records from table1, and the ID, name from table2 you're after.
Normally I would do this as a full outer join but since I think you want to reuse the name and id fields to relate to BOTH tables in the same column we had to use this approach and spell out all the column names in table 1 and put NULL for each column in table1 when displaying records from table2 in order to make the output of the second query union to the first. We simply can't use *
Option 2: Using a full outer join... with all columns from T1
SELECT T1.* FROM Table1 T1 FULL OUTER JOIN Table2 T2 on T1.ID = T2.ID and T1.Name = T2.Name WHERE (T1.ID is null or T2.ID is null)
you get this... which doesn't show Ringo...
But Then i would ask why you need anything from Table 2.... at all so I think you're wanting to still show the ID, Name from table2 when it doesn't exist in table1.
Which is why What I think you're after is the results from the 1st query using the union all..
Option 3 I suppose we could avoid the second query in option 1 by doing...
SELECT coalesce(T1.Name, T2.name) as name, coalesce(T1.Id,T2.ID) as ID, T1.col1, T1.Col2, T1.Col3 FROM Table1 T1 FULL OUTER JOIN Table2 T2 on T1.ID = T2.ID and T1.Name = T2.Name WHERE (T1.ID is null or T2.ID is null)
which gives us... as what I believe to be the desired results as well.
This works because we know we only want the name,id from table2 and all the column values in table1 will be blank.
Notice however in all cases we simply can't use Tablename.* to select all records from table1.
This is what you tried:
( SELECT name,id FROM table1 EXCEPT SELECT name,id FROM table2) UNION ALL ( SELECT name,id FROM table2 EXCEPT SELECT name,id FROM table1)