Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register


1
(click on this box to dismiss)

Does CUBRID support empty list in IN operators like "WHERE id IN()"?

I am generating in the code an SQL statement which has IN operator in the WHERE clause. IN operator should received a list of record IDs. However what will happen if that list is just empty?

MySQL correctly parses an empty IN() in which case it just says that nothing has been fond.

Does CUBRID accept empty list in IN operator?

link comment (1) accepted answer
asked 3 years ago
eye
91
A feature request has been created for this question at http://jira.cubrid.org/browse/ENGINE-72. - [Level:8]CUBRID 2 years ago
36 Answers
2

Seems it doesn't:

csql> select * from t where i in();

In line 1, column 28,

ERROR: syntax error, unexpected ')'


0 command(s) successfully processed.
csql> select * from t where i in(1);

=== <Result of SELECT Command in Line 1> ===

            i
=============
            1


1 rows selected.

Current transaction has been committed.

1 command(s) successfully processed.

link comment (0)
answered 3 years ago
sqlcopter
284
3

In my opinion, there is no point in passing an empty list to the IN operator. However, if you want to do it "just because", you can do it in CUBRID also:

 SELECT i FROM tbl WHERE i IN {};


The IN operator is defined in CUBRID as argument IN collection and is also designed to work on collection types.

Consider the following example that doesn't have such an elegant solution in MySQL:

CREATE TABLE students (id integer, name varchar(255), enlisted_in set of varchar(255));

INSERT INTO students (id, name, enlisted_in) VALUES(1, 'John', {'Math', 'English'});
INSERT INTO students (id, name, enlisted_in) VALUES(2, 'Mary', {'Math', 'Art'});
INSERT INTO students (id, name, enlisted_in) VALUES(3, 'Steven', {});

-- get students enlisted in Mathematics
SELECT name FROM students WHERE 'Math' IN enlisted_in; 


In MySQL, for this simple case, you have to use a many to many relationship:

CREATE TABLE students(id integer, name varchar(255));
CREATE TABLE student_courses (student_id integer, course_id integer);
CREATE TABLE courses(id integer, name varchar(255));

INSERT INTO students (id, name) VALUES(1, 'John');
INSERT INTO students (id, name) VALUES(2, 'Mary');
INSERT INTO students (id, name) VALUES(3, 'Steven');

INSERT INTO courses (id, name) VALUES(1, 'Math');
INSERT INTO courses (id, name) VALUES(2, 'Art');
INSERT INTO courses (id, name) VALUES(3, 'English');

INSERT INTO student_courses (student_id, course_id) VALUES(1, 1);
INSERT INTO student_courses (student_id, course_id) VALUES(1, 3);
INSERT INTO student_courses (student_id, course_id) VALUES(2, 1);
INSERT INTO student_courses (student_id, course_id) VALUES(2, 2);

SELECT s.name FROM students s, courses c, student_courses sc
WHERE
	c.name='Math'
	AND c.id = cs.course_id
	AND s.id = cs.student_id;



link comment (0)
answered 3 years ago
ginarrbrik
516
0

Since 9.0.0 (Apricot) release CUBRID allows empty lists in IN() operator. See issue ENGINE-72 for details.

link comment (0)
answered 2 years ago
dba
558
tagged




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: