String Concatenation in mySQL

[JPGargoyle]

Hi,

Does anyone know in MySQL, how to exclude one set of record from another set, which is similar to 'NOT IN' in some SQL language.
eg: select * from A where A. ID NOT IN (select ID from Table B)

Thanks a lot.

[Allan.d]

Hi,

select * from A where A. ID NOT IN (select ID from Table B)
Of * and
select * from A left join B on A.id = B.id where B.id is NULL;
* A lot of difference could ..
In particular, do not use select * want to do that .. Express it differently ..
select A. * from A, B where A.id = B.id (+) and B.id is NULL;

[JIII]

1.8.4.1 SubSELECTs

Subqueries have been implemented in MySQL version 4.1. See section 1.6.1 Features Available From MySQL 4.1.

MySQL Server until version 4.0 only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can, however, use the function IN () in other contexts.

You can often rewrite the query without a subquery:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

This can be rewritten as:

SELECT table1 .* FROM table1, table2 WHERE table1.id = table2.id;

The queries:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id = table2.id);

Can be rewritten as:

SELECT table1 .* FROM table1 LEFT JOIN table2 ON table1.id = table2.id
WHERE table2.id IS NULL;

Using a LEFT [OUTER] JOIN is generally much faster than an equivalent subquery because the server can optimise it better, a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things in those bygone days. But that is no longer the case, MySQL Server and many other modern database systems offer a whole range of outer joins types.

For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however, this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in subqueries). For this situation there are two options available:

The first option is to upgrade to MySQL version 4.1

The second option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)).

The third option is to use interactive SQL to construct a set of DELETE statements automatically, using the MySQL extension CONCAT () (in lieu of the standard | | operator). For example:

SELECT CONCAT ( 'DELETE FROM tab1 WHERE pkid =', " '", tab1.pkid, "'", ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;

You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter:

shell> mysql - skip-column-names mydb <myscript.sql | mysql mydb

MySQL Server 4.0 supports multi-table deletes that can be used to efficiently delete rows based on information from one table or even from many tables at the same time.

1.8.4.2 SELECT INTO TABLE

MySQL Server doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL Server supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing. See section 6.4.3.1 INSERT ... SELECT Syntax.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1 WHERE tblTemp1.fldOrder_ID> 100;

Alternatively, you can use SELECT INTO OUTFILE ... or CREATE TABLE ... SELECT.

1.8.4.3 Transactions and Atomic Operations

MySQL Server supports transactions with the InnoDB and BDB Transactional table handlers. See section 7 MySQL Table Types. InnoDB provides full ACID compliancy.

However, the non-transactional table types in MySQL Server such as MyISAM follow another paradigm for data integrity called `` Atomic Operations.''Atomic operations often offer equal or even better integrity with much better performance. With MySQL Server supporting both paradigms, the user is able to decide if he needs the speed of atomic operations or if he need to use transactional features in his applications. This choice can be made on a per-table basis.