# Existence Checks

- The common technique used to check whether a certain condition is met or not, obviously when no data needs to be returned, is simply COUNT how many records match that condition. A zero number indicates that no record has met that condition.
- Notice that this is not very efficient, as we are asking the database to scan the records for an accurate number that we do not really care about. All we really want to know if there is at least one such record and NOT how many they are.
- When the tables involved are of low-volume there should be no problem using this technique. It is very simple and uses common SQL syntax to all databases.
- However, when that condition is checked against a high-volume table that many of its records meet that condition, scanning all the matching records to get a count we don't need should be avoided.
- In this case use the EXISTS function as follows:

      Select 'x'
      From <The main table of the searched field, where it is defined as the PK of that table>
      Where <search field> = <search value> and
      EXISTS
      (<sub-select with the desired condition. This is the high volume table>);
      For example :
      Select 'x'
      From CI_UOM
      Where UOM_CD = input UOM_CD and
      EXISTS (select 'x'
      From CI_BSEG_CALC_LN
      Where UOM_CD = input UOM_CD);

- If this does not work for your special case, use the following option:

      Select 'x'
      From CI_INSTALLATION
      Where EXISTS
      (<sub-select with the desired condition>) ;

- Remember: This type of existence check using the Installation Options record should only be used in rare cases and should be consulted with the DBA first before implementation.
- Note that we use `CI_INSTALLATION`, since this table has only one row.

Parent topic: Composing SQL Statements