Is it possible to combine subqueries and joins in a single MYSQL statement?

Question

This question makes me confused. I'm trying to use Join and subquery in mysql but I keep getting syntax errors.

The statement in question is

SELECT Customer.customer_id, Customer.name, Order.address FROM Customer 
WHERE customer_id = (SELECT customer_id FROM Order WHERE customer_id = "625060836f7496e9fce3bbc6") 
INNER JOIN Order ON Customer.customer_id=Order.customer_id;

I tried using just the query without subquery and it worked fine.

SELECT Customer.customer_id, Customer.name, Order.address FROM Customer  
INNER JOIN Order ON Customer.customer_id=Order.customer_id;

It is also possible to delete the join but keep the subquery.

SELECT Customer.customer_id, Customer.name, Order.address FROM Customer 
WHERE customer_id = (SELECT customer_id FROM Order WHERE customer_id = "625060836f7496e9fce3bbc6")

Just using a subquery and a join together will result in a syntax error

I can't seem to find the error.

What am I doing wrong here.

Thanks in advance

Answer 1

The secret is correct syntax!

When querying multiple tables, it is best to use aliases to reference them, and string literals should also be separated by single quotes in cases where multiple tables share the same column name.

In this specific example, the subquery is redundant, just use the string literal directly in the where clause.

SELECT c.customer_id, c.name, o.address 
FROM Customer c
JOIN Order o ON c.customer_id = o.customer_id
WHERE c.customer_id = (
  SELECT customer_id 
  FROM Order 
  WHERE customer_id = '625060836f7496e9fce3bbc6'
);