0

I'm looking at the example

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

from W3Schools and am wondering how I'm supposed to group the clauses in my mind. From what I understand, every SQL query returns a table, and clauses within the query may themselves return a table. So I think of the whole 

Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID

as being a table and I'm returning a sub-table of it by applying SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate to it. Is that the right way to think about things?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • You said : `as being a table and I'm returning a sub-table of it by applying SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate` The first part is correct. But I wouldn't say you are returning a sub-table from it. You're just grabbing the columns you want – Caffeinated Sep 28 '15 at 16:21
  • Yeah. And best way to visualize it is by creating tables in database and `select * from` both tables one by one and see the data. Then use the join and how it is changing your output. And out of all those columns, just view the one you need. If you don't have any db installed on your system, use `Head first SQL` hands on webpage or http://sqlfiddle.com/ . Once you start selecting tables and start putting where conditions and joins and see the output visually, then you will have a lot better understanding. – Utsav Sep 28 '15 at 16:27
  • See if this helps: http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by/3841485#3841485 – mustaccio Sep 29 '15 at 01:05

2 Answers2

1

You can think of every query as if it composes a new temporary table with the result you want. This one takes rows from Orders and from Customers, matches them according to the CustomerID field in both tables (the on clause of the join), and then returns just several fields from Orders (OrderID and OrderDate) and one field from Customers (CustomerName).

Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

Basically, you are returning a new table containing all of the columns in your SELECT statement. If you simplify the query to:

SELECT OrderID, OrderDate
FROM Orders

You'd get a printout of the entire Orders table, but with only the two columns you specified.

With your INNER JOIN, you are filtering out the results to only include the Orders that have a CustomerID that matches a similar row in the Customers table with that same CustomerID. And with your SELECT statement, you are adding a column that shows the customer's name that is matched up with those orders.

So the ON part of the INNER JOIN is just matching customers to their respective orders. The SELECT part is where you are creating the new output table with the three columns you are interested in.

Does that help?

amklose
  • 1,143
  • 8
  • 15