0

I am trying to understand the JOIN command from w3schools tutorial and there is an example. Can you please "translate" it to me what exactly it does? I already know what do the dots do, but INNER JOIN, ON and so on messed me up?

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

And does it creates a new table in my SQL database or it just creates (lets call it..) "virtual" table which I can use it in the moment

Bocho Todorov
  • 429
  • 1
  • 9
  • 22
  • 1
    It retrieves all rows from the `orders` table and the corresponding rows from the `customers` table. –  Jan 03 '14 at 23:11

4 Answers4

1

The dot notation here signifies the column of a table. 

Table.Column

So the SELECT is retrieving the columns specified from each table. JOIN matches the tables based on the condition that appears after ON this queries joins customers to orders based the condition of having the same CustomerID.

For more on joins check out http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Sam
  • 2,761
  • 3
  • 19
  • 30
0

Perhaps the first thing to understand is the "dot notation." When you see a value like Orders.CustomerID SQL will read that to mean the CustomerID column within the Orders table.

The INNER JOIN is looking for an exact match for the records you specify with the ON clause...

So, in this example, SQL will first find all the records in the Orders table which have a CustomerID that matches the CustomerID found in the Customers table.

Then, it will look to the SELECT clause and show you the OrderID (from the Orders table), the CustomerName (from the Customers table) and the OrderDate from the Orders table.

If you're using SQL Server Management Studio (SSMS) or some other similar tool, it should display your results - kindof like a virtual table... but it doesn't actually create a table.

Hope that helps,

Russell

Russell Schutte
  • 237
  • 2
  • 4
  • 12
0

There are INNER AND OUTER JOINS. I think this post sums up those differences well.

What is the difference between "INNER JOIN" and "OUTER JOIN"?

As far as the concept itself you are 'joining' the tables by finding things in common between them based on a shared value in each. If you have ever done a vlookup with Microsoft Excel then you are familiar with this concept. In the query you've posted you are selecting values from both the Orders and Customers table. You can tell which table you are selecting values from by the prefix Orders. or Customers. You can then tell which column by the value following the dot i.e. OrderID, CustomerName, OrderDate.

The way the query knows to pull a conjoined field is if the CustomerID field matches.

So step by step

You are selecting

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

From the table Orders

FROM Orders

You wish to pull corresponding values from Customers. There are numerous join methods and logic that goes with each but you have chosen INNER JOIN explained in the link above.

INNER JOIN Customers

The criteria for joining is CustomerID

ON Orders.CustomerID=Customers.CustomerID;

To use an approximate Excel Vlookup analogy

The Lookup_value is CustomerID

The Table array is ORDERS

The range lookup is the type of join you are performing

Community
  • 1
  • 1
Jay C
  • 842
  • 6
  • 17
  • 37
0

SELECT statement returns data, it doesn't create any table not even virtual table whatsoever (if you see any table after executing SELECT statement, thats just a way the returned data presented). Create and modify table done using DDL (Data Definition Language) statements when SELECT is one of DML (Data Manipulation Language) [reference].

Join statement explained very well here, from basic use to more advance with illustration.

har07
  • 88,338
  • 12
  • 84
  • 137