How to select rows with no matching entry in another table?

Posted on

How to select rows with no matching entry in another table? – This article will take you through the common SQL errors that you might encounter while working with sql, foreign-keys,  . The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

I’m doing some maintenance work on a database application and I’ve discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there’s no foreign key constraints on the tables.

I’m trying to add FK constraints on these columns, but I’m finding that, because there’s already a whole load of bad data in the tables from previous errors which have been naively corrected, I need to find the rows which don’t match up to the other table and then delete them.

I’ve found some examples of this kind of query on the web, but they all seem to provide examples rather than explanations, and I don’t understand why they work.

Can someone explain to me how to construct a query which returns all the rows with no matches in another table, and what it’s doing, so that I can make these queries myself, rather than coming running to SO for every table in this mess that has no FK constraints?

Solution :

Here’s a simple query:

FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID

The key points are:

  1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

  2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null – in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

I would use EXISTS expression since it is more powerful, you can e.g. more precisely choose rows you would like to join. In the case of LEFT JOIN, you have to take everything that’s in the joined table. Its efficiency is probably the same as in the case of LEFT JOIN with null constraint.

FROM Table1 t1

SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

Table 1 has a column that you want to add the foreign key constraint to, but the values in the foreign_key_id_column don’t all match up with an id in table 2.

  1. The initial select lists the ids from table1. These will be the rows we want to delete.
  2. The NOT IN clause in the where statement limits the query to only rows where the value in the foreign_key_id_column is not in the list of table 2 ids.
  3. The SELECT statement in parenthesis will get a list of all the ids that are in table 2.

Let we have the following 2 tables(salary and employee)
enter image description here

Now i want those records from employee table which are not in salary.
We can do this in 3 ways:

  1. Using inner Join
select * from employee
where id not in(select from employee e inner join salary s on

enter image description here

  1. Using Left outer join
select * from employee e 
left outer join salary s on  where is null

enter image description here

  1. Using Full Join
select * from employee e
full outer join salary s on where not in(select id from salary)

enter image description here

Where T2 is the table to which you’re adding the constraint:

WHERE constrained_field NOT
IN (
    SELECT DISTINCT t.constrained_field
    FROM T2 
    USING ( constrained_field )

And delete the results.

From similar question here MySQL Inner Join Query To Get Records Not Present in Other Table I got this to work

SELECT * FROM bigtable 
LEFT JOIN smalltable ON = 

smalltable is where you have missing records, bigtable is where you have all the records. The query list all the records that not exist in smalltable but exists on the bigtable. You could replace id by any other matching criteria.

I Dont Knew Which one Is Optimized (compared to @AdaTheDev
) but This one seems to be quicker when I use (atleast for me)

SELECT id  FROM  table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2

If You want to get any other specific attribute you can use:

SELECT COUNT(*) FROM table_1 where id in (SELECT id  FROM  table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2);

You could opt for Views as shown below:

CREATE VIEW AuthorizedUserProjectView AS select t1.username as username, as useremail, as projectid, 
(select m.role from userproject m where m.projectid = and m.userid = as role 
FROM authorizeduser as t1, project as p

and then work on the view for selecting or updating:

select * from AuthorizedUserProjectView where projectid = 49

which yields the result as shown in the picture below i.e. for non-matching column null has been filled in.

[Result of select on the view][1]

You can do something like this

   SELECT IFNULL(`price`.`fPrice`,100) as fPrice,product.ProductId,ProductName 
          FROM `products` left join `price` ON 
          price.ProductId=product.ProductId AND (GeoFancingId=1 OR GeoFancingId 
          IS NULL) WHERE Status="Active" AND Delete="No"

SELECT * FROM First_table
SELECT * FROM another

    select * from [dbo].[EmppDetails] e
     right join [Employee].[Gender] d on e.Gid=d.Gid
    where e.Gid is Null

    select * from [dbo].[EmppDetails] e
     left join [Employee].[Gender] d on e.Gid=d.Gid
    where d.Gid is Null

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.

Leave a Reply

Your email address will not be published. Required fields are marked *