Returning Result Even for Elements in In List That Don't Exist in Table

Returning result even for elements in IN list that don't exist in table

From the SQL side you could define a table type and use that to join to your real data, something like:

create type my_array_type as table of number
/

create or replace function f42 (in_array my_array_type)
return sys_refcursor as
  rc sys_refcursor;
begin
  open rc for
    select a.column_value as id,
      case when t.id is null then 'missing'
        else 'present' end as status
    from table(in_array) a
    left join t42 t on t.id = a.column_value
    order by id;

  return rc;
end f42;
/

SQL Fiddle demo with a wrapper function so you can query it directly, which gives:

        ID STATUS             
---------- --------------------
         1 present              
         2 present              
         3 present              
         4 missing              
         8 missing              
        23 present              

From Java you can define an ARRAY based on the table type, populate from a Java array, and call the function directly; your single parameter bind variable is the ARRAY, and you get back a result set you can iterate over as normal.

As an outline of the Java side:

int[] ids = { 1, 2, 3, 4, 8, 23 };
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("MY_ARRAY_TYPE",
  conn);
oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);

cStmt = (OracleCallableStatement) conn.prepareCall("{ call ? := f42(?) }");
cStmt.registerOutParameter(1, OracleTypes.CURSOR);
cStmt.setArray(2, ora_ids);
cStmt.execute();
rSet = (OracleResultSet) cStmt.getCursor(1);

while (rSet.next())
{
    System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
}

Which gives:

id 1: present
id 2: present
id 3: present
id 4: missing
id 8: missing
id 23: present

As Maheswaran Ravisankar mentions, this allows any number of elements to be passed; you don't need to know how many elements there are at compile time (or deal with a theoretical maximum), you aren't limited by the maximum number of expressions allowed in an IN or by the length of a single delimited string, and you don't have to compose and decompose a string to pass multiple values.

---

As ThinkJet pointed out, if you don't want to create your own table type you can use a predefined collection, demonstrated here; the main function is the same apart from the declaration of the parameter:

create or replace function f42 (in_array sys.odcinumberlist)
return sys_refcursor as
...    

The wrapper function populates the array slightly differently, but on the Java side you only need to change this line:

ArrayDescriptor aDesc =
  ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );

Using this also means (as ThinkJet also pointed out!) that you can run your original stand-alone query without defining a function:

select a.column_value as id,
case when t.id is null then 'missing'
else 'present' end as status
from table(sys.odcinumberlist(1, 2, 3, 4, 8, 23)) a
left join t42 t on t.id = a.column_value
order by id;

(SQL Fiddle).

And that means you can call the query directly from Java:

int[] ids = { 1, 2, 3, 4, 8, 23 };
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );
oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);

sql = "select a.column_value as id, "
    + "case when t.id is null then 'missing' "
    + "else 'present' end as status "
    + "from table(?) a "
    + "left join t42 t on t.id = a.column_value "
    + "order by id";
pStmt = (OraclePreparedStatement) conn.prepareStatement(sql);
pStmt.setArray(1, ora_ids);
rSet = (OracleResultSet) pStmt.executeQuery();

while (rSet.next())
{
    System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
}

... which you might prefer.

There's a pre-defined ODCIVARCHAR2LIST type too, if you're actually passing strings - your original code seems to be working with strings even though they contain numbers, so not sure which you really need.

Because these types are defined as VARRAY(32767) you are limited to 32k values, while defining your own table removes that restriction; but obviously that only matters if you're passing a lot of values.

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

T-SQL: How to Select Values in Value List that are NOT IN the Table?

For SQL Server 2008

SELECT email,
       CASE
         WHEN EXISTS(SELECT *
                     FROM   Users U
                     WHERE  E.email = U.email) THEN 'Exist'
         ELSE 'Not Exist'
       END AS [Status]
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  

For previous versions you can do something similar with a derived table UNION ALL-ing the constants.

/*The SELECT list is the same as previously*/
FROM (
SELECT 'email1' UNION ALL
SELECT 'email2' UNION ALL
SELECT 'email3' UNION ALL
SELECT 'email4'
)  E(email)

Or if you want just the non-existing ones (as implied by the title) rather than the exact resultset given in the question, you can simply do this

SELECT email
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  
EXCEPT
SELECT email
FROM Users

Find records from one table which don't exist in another

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

This is the shortest statement, and may be quickest if your phone book is very short:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatively (thanks to Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

or (thanks to WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

Get records from Table A, that are not in Table B

The following query will give you all the ids of people in tableA that are not in tableB based on full name and adress:

SELECT tableA.id FROM tableA
LEFT OUTER JOIN tableB
-- people are the same if fullname and adress match
ON tableA.`full_name` = tableB.`full_name`
   AND tableA.adress = tableB.adress
-- filter people that re in tableA only
WHERE tableB.`id` IS NULL
-- filter duplicates
GROUP BY tableA.id

You can easily edit this selet to include whatever information you need from tableA.

SELECT that returns list of values not occurring in any row

Given the numbers are a fixed list. Quickest way I can think of is have a test table, populated with those numbers and do

untested select statement - but you will follow the princpal.

select test.number 
from test 
left join 
    users 
on 
    test.number = users.id 
where test.number <> users.id

Then you'll get back all the numbers that dont have a matching user.id and so can fill in the holes..

A quick way to return list without a specific element in Python

>>> suits = ["h","c", "d", "s"]
>>> noclubs = list(suits)
>>> noclubs.remove("c")
>>> noclubs
['h', 'd', 's']

If you don't need a seperate noclubs

>>> suits = ["h","c", "d", "s"]
>>> suits.remove("c")

Check if value from given list in sql is existing in table - SQL

Your image doesn't really help I'm afraid but I think you need to know about LEFT OUTER JOINS and NULL values. I think these code samples will help explain.

Firstly this is my demo data

declare @list1 table (id int, itemname varchar(30));
insert into @list1 (id, itemname) values
(1, 'item 1'),(2,'item 2'), (3, 'item 3'),
(4, 'item 4'),(5,'item 5'), (6, 'item 6');

declare @list2 table (id int, itemname varchar(30));
insert into @list2 (id, itemname) values
(1, 'item 1'),(4,'item 4'), (6, 'item 6');

As you can see List 2 only contains 3 of the items that are on List 1.
To list items that are in both lists do as you have already done and use and INNER JOIN

select l1.id, l2.itemname
from @list1 l1
INNER JOIN @list2 l2 on l1.id = l2.id;

But to list items from list1 that are NOT in list2 you need to use and OUTER JOIN and check for NULL values coming from List 2

select l1.id, l1.itemname
from @list1 l1
LEFT OUTER JOIN @list2 l2 on l1.id = l2.id
WHERE l2.id IS NULL;

A LEFT outer join lists every record on the left side of the join (the first table listed in the FROM clause). If there is a matching row on the right side of the join (the next table in the list, i.e. List 2) then we can get the values from the matching row, but if there is no match then SQL returns NULL as the value. We used that in the WHERE clause above, but we can use it in other ways.

E.g. To list all items with indication of whether in list 2 or not - this will list every item and next to each will show either the name on list 2 or the words "Not in list 2"

select l1.id, l1.itemname, ISNULL(l2.itemname, 'Not in list 2')
from @list1 l1
LEFT OUTER JOIN @list2 l2 on l1.id = l2.id;

Alternatively, for neater output, this will only show "Not in list 2" next to the missing items and blanks for matching items

select l1.id, l1.itemname, 
CASE WHEN l2.itemname IS NULL THEN 'Not in list 2' ELSE '' END as [Flag]
from @list1 l1
LEFT OUTER JOIN @list2 l2 on l1.id = l2.id;

select a value where it doesn't exist in another table

You could use NOT IN:

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)

However, meanwhile i prefer NOT EXISTS:

SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)

There are other options as well, this article explains all advantages and disadvantages very well:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

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

Here's a simple query:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

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.

---

Related Topics