Pedro Martins Novaes Pedro Martins Novaes - 4 months ago 18
SQL Question

SQL: INNER JOIN won't work after WHERE statement?

I have two tables in MySQL - They are very similar to these on this example. What I want to do is practly the same thing, with one difference. Instead of doing this query:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;


and selecting the whole goddamn table and then doing the INNER JOIN, I want to do this:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
------> WHERE OrderDate = '1996-07-05'
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;


Btw, the arrow is just there to help you spot the difference.

But that does not work. I get the following error:


1064 - 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 'INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID' at line 5


That piece of code would allow me to have a table with only the content that I want, and the INNER JOIN done it only where I wanted. How do I do this?

To be clear: I want to first select things WHERE... and then do the inner join in the same query.

Answer

Don't make up syntax rules. It won't work.

In all implementations of SQL, the WHERE clause goes after all the table references in your query.

https://mariadb.com/kb/en/sql-99/select-statement/

This has nothing to do with the way the RDBMS engine chooses to optimize fetching of rows. The RDBMS will change the order it accesses tables and rows to try to optimize for you.