How to check if every primary key value is being referenced as foreign key in another table

I have two table tb1 and tb2 . id in tb1 is pk and referenced as fk in tb2. I want to know if tb1 has id values as 1,2,3,4,5 and tb2 has fk_values for 1,2,3,4 but doesn't have for pk 5 how can i find this.

tb1 ------ id ------ 1 ------ 2 ------ 3 ------ 4 ------ while tb2 fk_id ------- 1 -- 1 -- 2 -- 3 -- 3 --

but table 2 doesn't have values for 4 then how can i find out the 4 value here .

Database to be used is mysql.


To find what's in tb1 but not in tb2 do this:

SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.fk_id WHERE tb2.fk_id IS NULL

To do it the other way (in tb2 but not in tb1 ), which cannot be in this case because, there is a foreign key, but none the less it might be useful for you later simply switch the two tables

SELECT tb2.* FROM tb2 LEFT JOIN tb1 ON tb1.id = tb2.fk_id WHERE tb1.id IS NULL


use the left join .left join will give you the result that only exists in the left table.which means you must use tb1 left join tb2 like this.

// pick the tb1.id in the result when the fk_id is null. select tb1.id from tb1 left join tb2 on tb1.id = tb2.fk_id where tb2.fk_id is null;


  • javascript multiple input textbox validation
  • How do I remove duplicates in paging
  • Mysql - Summing counts of multiple joined tables
  • complex query join checking that value does not exist
  • PDO error when wrong host name
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Moving mysql files across servers
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • Recording logins for password protected directories
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Counter field in MS Access, how to generate?
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to extract text from Word files using C#?
  • Sending data from AppleScript to FileMaker records
  • How to handle AllServersUnavailable Exception
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • jquery mobile loadPage not working
  • How to get next/previous record number?
  • Comma separated Values
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • python regex in pyparsing
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can i traverse a binary tree from right to left in java?