Re: Technical Clarification
Hi,
On Mon, Mar 19, 2001 at 01:23:56AM -0600, Jason Landry wrote:
I know that this response was directly related to a question about
subqueries, but I think it's important to point this out, because I almost
didn't pursue this avenue as a result.
As Fred pointed out, MySQL does not support IN and NOT IN, but *only* in the
case of subqueries:
select * from table 1 where id in (select id from table2)
However, it DOES support IN and NOT IN as long as a subquery is not used:
select * from table 1 where id in (115,515,614,23,51,24,15,12,51,23)
Ah yes, thanks for correcting me. I use the constant IN frequently as well.
It can even be useful in some cases to replace sub-queries by serialized
queries if you know the result isn't a very large set of values.
I believe MySQL optimizes matches against the list of values by putting them
in a hash table, but by doing two queries you'll send the result of the first
('sub'-select) back and forth which is not very efficient. You may even bump
into the size limit of query packets (16M).
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Technical Clarification
Hi Kathir, On Mon, Mar 19, 2001 at 05:38:55AM -, Rama kathiresan kathiresan wrote: This is kathir from Uniware Systems, We are developing project on ASP with Backend as MySQL, We having a problem using "IN" and "NOT IN" operators in sub query ... iam selecting the values from 3 different tables using in and not in operators. When iam executing the above query it displaying the error as follows: [TCX][MyODBC]You have an error in your SQL syntax near 'CRXU9021208'')' at line 1 MySQL doesn't support IN and NOT IN. The manual and mailing lists describe alternatives for many cases, but these are less readable (and maybe slower). Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Technical Clarification
Greetings and salutations, EverybodySingAlong MySQL Supports FKs. A FK is a table's PK value, stored in another table in order to relate the records together. It does NOT support FK CONSTRAINTS. (Cascading deletes, restricts, etc.) /EverybodySingAlong ...and now for the opinion portion of our show... For all the of databases I've worked on for the past 16 years, this is not a deal killer. It just means that I, as the developer, have to do my job. IN a LOT of ways, FK constraints are bad, evil things that were designed so programmers could slack. :) Kidding, of course, but if not implemented properly, they can cause problems and they ALWAYS cost performance. Currently, my largest MySQL databases is a whopping 30 tables! (ok, so I'm not working on a grand scale here!) But 90% of my tables have FKs in them from other tables. The only difference between this database and it's MSSQL sibling (I'm porting) is that I have to manually cascade my deletes. But since my license for MSSQL was going to be $5k+ this year, it's worth it! :) Stored Procedures, IMHO, should be a much higher priority for the development team. Cal http://www.calevans.com -Original Message- From: gopinath [mailto:[EMAIL PROTECTED]] Sent: Monday, March 12, 2001 3:16 AM To: [EMAIL PROTECTED] Subject: Technical Clarification Hi, We are doing a project with combination of ASP MYSQL (backend). We are having some problem for setting relationship between the tables. We have to know whether foreign key will be supported in MySQL and we came to know that there is no use for foreign key(as per your documentation: The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references.) pls.. let us know whether we can use foreign key to access master table records in child tables. if there is any possiblity pls... provide solution for us. awaiting ur reply, at the earliest. regards, kathir. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
