I cannot use the INTERSECT operator

Question

I'm trying to resolve this exercise:

'Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.'

I tried resolving it using the INTERSECT operator like this:

SELECT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) = 'A' OR LEFT(CITY, 1) = 'O'
OR LEFT(CITY, 1) = 'E' OR LEFT(CITY, 1) = 'I' OR
LEFT (CITY, 1) OR LEFT(CITY, 1) = 'U'

INTERSECT

SELECT CITY
FROM STATION
WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1) = 'O'
OR RIGHT(CITY, 1) = 'E' OR RIGHT(CITY, 1) = 'I' OR
RIGHT (CITY, 1) OR RIGHT(CITY, 1) = 'U';

But I receive the following error message:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT
SELECT CITY
FROM STATION
WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1)' at line

What am I doing wrong and what other way do I have to solve this exercise?

You should learn to use parentheses for boolean expressions: `WHERE (LEFT(CITY, 1) = 'A' OR ...) AND (RIGHT(CITY, 1) = 'A' OR ...)`. Or use regex as GMB suggested. — Paul Spiegel, Jun 20 '20 at 22:57

score 1 · Answer 1 · answered Jun 20 '20 at 22:51

You can use join:

SELECT CITY1 AS CITY
FROM
(
    SELECT CITY AS CITY1
    FROM STATION 
    WHERE LEFT(CITY, 1) = 'A' OR LEFT(CITY, 1) = 'O'
    OR LEFT(CITY, 1) = 'E' OR LEFT(CITY, 1) = 'I' OR
    LEFT (CITY, 1) OR LEFT(CITY, 1) = 'U'
) AS T1 JOIN (
    SELECT CITY AS CITY2
    FROM STATION
    WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1) = 'O'
    OR RIGHT(CITY, 1) = 'E' OR RIGHT(CITY, 1) = 'I' OR
    RIGHT (CITY, 1) OR RIGHT(CITY, 1) = 'U'
) AS T2 ON CITY1 = CITY2

score 1 · Accepted Answer · answered Jun 20 '20 at 22:52

Query the list of CITY names from STATION which have vowels as both their first and last characters.

You seem to be overcomplicating this. As I understand the question, you just need a where clause that filters the table once - regexes come handy for this:

select city
from station
where city regexp '^[aeiou].*[aeiou]$'

The pattern describes a string that starts and ends with a wovel (^ represents the beginning of the string, and $ is the end).

If you have duplicate city in the table, then use select distinct city from ... instead.

If you want to use left() and right():

select city
from station
where 
    right(city, 1) in ('a', 'e', 'i', 'o', 'u')
    and left(city, 1) in ('a', 'e', 'i', 'o', 'u')

I cannot use the INTERSECT operator

2 Answers2