How to determine if a table contains a value in SQL?

Question

I feel like I'm missing something very obvious here, but it seems that the only way to go about doing this is to get the value, and then see if it returns a null (empty) value, which I would rather not do.

Is there an equivalent to List.contains(Object o) in SQL? Or perhaps the JDBC has something of that nature? If so, what is it?

I am using Microsoft Access 2013.

Unfortunately I don't have any useful code to show, but here is the gist of what I am trying to do. It isn't anything unique at all. I want to have a method (Java) that returns the values of a user that are stored in the database. If the user has not previously been added to the database, the user should be added, and the default values of the user should be set. Then those newly created values will be returned. If a player has already been added to the database (with the username as the primary key), I don't want to overwrite the data that is already there.

---

I would also advise against using MS Access for this purpose, but if you are familiar with MS Office applications, the familiar UI/UX structure might help you get your footing and require less time to learn other database environments. However, MS Access tends to be quite limited, and I would advise considering alternative options if available.

Solution 1

The only way to see if an SQL table contains a row with some condition on a column is to actually make an SQL query. I don't see why you wouldn't do that. Just make sure that you have an index on the column that you will be constraining the results on. Also for better speed use count to prevent from retrieving all the data from the rows.

SELECT count(*) FROM foos WHERE bar = 'baz'

Assuming you have an index on the bar column this query should be pretty fast and all you have to do is check whether it returns > 0. If it does then you have rows matching your criteria.

Solution 2

You can use "IF EXISTS" which returns a boolean value of 1 or 0.

select
  if(
    exists( select * from date1 where current_date()>now() ),
    'today > now',
    'today is not > now' 
  ) as 'today > now ?' ;

+--------------------+
| today > now?       |
+--------------------+
| today is not > now |
+--------------------+
1 row in set (0.00 sec)

Another Example:

SELECT IF( 
         EXISTS( SELECT col from tbl where id='n' ),
         colX, colY 
       ) AS 'result'
FROM TBL;

Solution 3

I'm also new to sql and I'm using Oracle.

In Oracle, suppose we have: TYPE: value.

We can use:

where value not in (select TYPE from table)

to make sure value not exist in the column TYPE of the table.

Don't know if it helps.