CODEWITHSUNDEEP
mysqlor-operator
Master
Master

Reputation: 425

Same MySQL query but different outputs - why?

I use the same query in my book. Then, why is my output much different from that given in the book ? The book also says "the || operator does not lead to the concatenation of alphanumeric values, but it is regarded as an OR operator to combine predicates". What does the "an OR operator to combine predicates" mean ?

......etc

Upvotes: 0

Views: 184

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

In MySQL, you have to use CONCAT() to bring the values of the columns together in one string:

SELECT playerno, CONCAT(town, ' ', street, ' ', houseno)
FROM   players 
WHERE  town = 'stratford';

Your book is perhaps referring to the Oracle concatenation syntax which uses || to concat strings. MySQL, by default, treats || as an OR operator between two or more conditions, returning 1 if either of them evaluate to "true".

But as noted in satdev86's answer, MySQL does permit the use of || as a concatenation operator only if the mode PIPES_AS_CONCAT is manually set before you execute your query.

EDIT:

In your book, it clearly states you have to set the MySQL sql_mode for the examples to work. It's on the page prior to your SQL example:

http://books.google.com/books?id=c5G42OHT96cC&pg=PT160&lpg=PT160&dq=%22This+specification+is+needed+for+the+following+examples.+It+applies+only+to+the+current+session.%22&source=bl&ots=6b6zeFbM-2&sig=3a54S4vbgmKbPBqVbbR8OdxLLI8&hl=en&sa=X&ei=Nwz9T6fKAcfI2gWZhfDTBg&ved=0CCEQ6AEwAA

Upvotes: 0

satdev86
satdev86

Reputation: 810

Because, As per book, if they have used a sql mode PIPES_AS_CONCAT, then your pipes will act as concatenation operator.

SET sql_mode='PIPES_AS_CONCAT';
select playerno, town || '' || street || '' || houseno
from players 
where town = 'stratford';

will give you the output as you see in book :)

Upvotes: 1

Related Questions