Enter your Sign on user name and password.

Forgot password?
Sign In | Subscribe
Start learning today, and be successful in your academic & professional career. Start Today!
Loading video...
This is a quick preview of the lesson. For full access, please Log In or Sign up.
For more information, please see full course syllabus of Advanced PHP
  • Discussion

  • Study Guides

  • Download Lecture Slides

  • Table of Contents

  • Transcription

  • Related Services

Start Learning Now

Our free lessons will get you started (Adobe Flash® required).
Get immediate access to our entire library.

Sign up for Educator.com

Membership Overview

  • Unlimited access to our entire library of courses.
  • Search and jump to exactly what you want to learn.
  • *Ask questions and get answers from the community and our teachers!
  • Practice questions with step-by-step solutions.
  • Download lesson files for programming and software training practice.
  • Track your course viewing progress.
  • Download lecture slides for taking notes.
  • Learn at your own pace... anytime, anywhere!

SQL: Joins

  • MySQL provides the DATETIME data type for storing a timestamp.
  • The PHP date() function can be used to generate properly formatted DATETIME strings: ‘YYYY-MM-DD HH:MM:SS’.
  • It is advantageous to store timestamps as DATETIME data rather than as simple strings, like VARCHAR, because MySQL can perform date calculations on DATETIME columns to restrict the result sets of queries.
  • A join enables us to extract data from more than one table in a single SELECT query.
  • The basic type of join is called an inner join and is specified by using the INNER JOIN SQL keywords:
    SELECT * FROM orders INNER JOIN customers;
  • An inner join forms the Cartesian product of all of the rows from all tables specified, meaning that the result set will contain a row for each combination of the rows from one table with all of the rows from the other tables.
  • A join condition can be provided using the ON keyword in order to restrict the rows present in an inner join result set based on specified criteria. For example:
    SELECT * FROM orders INNER JOIN customers ON orders.custID=customers.custID;
  • Join queries involve multiple tables so to specify a column from one of the tables in a join, you use the syntax:
  • The join condition specified after the ON keyword functions the same way that a where condition does.
  • An INNER JOIN can also be implicitly defined by using a WHERE clause in the following manner:
    SELECT * FROM orders, customers WHERE orders.custID=customers.custID;
  • In queries using joins, it is often useful to define aliases, which is a shortened way to refer to a table in a query. In SQL, aliases are defined using the AS keyword:
    SELECT o.orderID, c.firstName, c.lastName
    FROM orders AS o
    INNER JOIN customers AS c
    ON o.custID=c.custID;
  • Additional Resources:

SQL: Joins

Lecture Slides are screen-captured images of important points in the lecture. Students can download and print out these lecture slide images to do practice problems as well as take notes while watching the lecture.

  • Intro 0:00
  • Lesson Overview 0:11
    • Lesson Overview
  • DATETIME Data Type 1:52
    • DATETIME Data Type
  • Modeling Orders 5:12
    • Modeling Orders
  • Customers Table 10:46
    • Customers Table
  • Example 16:18
    • Example: Order Table in MySQL
  • Joins 22:26
    • Joins Overview
    • Inner Join
    • Example: Joins
  • Join Conditions 28:14
    • Join Conditions
    • Example
    • Join Queries
    • Example: Join Conditions in MySQL
  • Implicit Joins 34:42
    • Implicit Joins
    • Example: Implicit Joins
  • Aliases 37:28
    • Introduction to Aliases
    • Example: Aliases
  • Required Homework 40:32
    • Problem 1
    • Problem 2
    • Problem 3

Transcription: SQL: Joins

Welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be talking about a SQL concept known as JOINS,0004

which is going to allow us to query data from multiple tables at the same time.0007

Before we get into working with JOINS in particular, we are going to talk about a couple topics.0013

The first thing that we are going to do is introduce a new SQL type for columns in our database, called DATETIME,0019

which is going to allow us to store a timestamp, a date/time value.0027

And then, we are going to talk about how, currently, in our web application, when a user creates an order0032

or submits an order in the store, it emails the store administrator the information about the order.0038

Well, we are going to be changing that system, in our next version of the web application,0044

to where it is just going to store the order information in the database, as opposed to sending an email.0048

We are going to discuss, in today's lesson, about how we might model orders within our database.0053

It is going to involve an Orders table; it is going to involve a Customers table.0059

And then, that is going to bring us to our concept of JOINS, which is where we are going to be learning0063

how to write queries that get information from multiple tables.0069

And that is going to relate to how we pull information about orders from our database.0072

As far as JOINS goes, we are going to talk about something called the join conditions, which is like a WHERE condition.0077

It is going to allow us to specify what data we want to pull from multiple tables.0082

We are going to talk about two other related concepts: one known as an implicit joins and something known as aliases,0086

which are going to make your SQL statements cleaner-looking.0093

And then, also, we are going to go over required homework, which is going to have you set up the tables in your web store database0096

so that we will be able to run our next version of the web application, which is going to store orders within the MySQL database.0105

The first thing we are going to talk about, again, is a new data type called DATETIME.0114

It is a MySQL data type, and it allows you to store a timestamp.0119

For example, if we wanted to create a table called Orders, it might have a number of different columns.0123

And this is an abbreviated CREATE TABLE statement.0129

Let's say we have an order ID that is an unsigned integer, and I left it out...maybe it is a primary key that is on AUTO_INCREMENT.0131

But that is just one column.0137

And then, let's say we have a column called dateTimeOrdered, which is going to be the date and time that an order was processed.0139

Well, we can store that as a data type called DATETIME.0147

And what that does is stores a date and time stamp for that column.0152

For example, in MySQL, it stores them in this format here, where you have the four-digit year, two-digit month, and two-digit day,0158

and then you have two digits for hours, minutes, and seconds.0170

And what you can do is: we are going to be creating a timestamp when an order is processed.0174

Currently, we had been using the date function within our emailOrder function to generate a timestamp on our order.0180

For example, when we place an order in our current web store, it shows you a date.0189

It says your order was placed at so-and-so time.0194

Well, we are still going to use that date function, but we are going to be storing it in the database.0197

We are going to have to specify how we create the date string, because for MySQL, it needs to be in this format here.0201

And we are going to be using date...as we know, with the date function, you provide it some sort of formatting string.0211

that is going to allow you to describe how you want the date stored.0219

When we add an order to our database from PHP (which we are going to see in our next web application),0224

we are going to be using the date function to generate this date string that we are going to store in the database.0229

One thing to note is that you could also just store dates as regular strings in the database.0235

For example, this right here is just a regular string, in essence.0240

You could have...and just have that be a string, and then store that in your database, and be able to extract information from it.0244

Well, there is a distinct advantage to using the DATETIME data type, as opposed to maybe using a VARCHAR, or...0259

you could use even a CHAR for this type of data, because it is going to be of a known length.0267

in that, because MySQL will know that the column data represents a DATETIME and is not just a string, you can perform operations on it.0274

For example, you will be able to do things like: let's say you want to query your database0284

and say, "Give me all the orders that happened in the last month."0291

Well, MySQL will be able to use the fact that a particular column is a date/time column to be able to develop a query with results based on criteria you specify.0294

If you had just specified it as a VARCHAR, for example, MySQL won't be able to do that.0306

How could we model orders in our database? Orders contain a bunch of different information.0314

We know that, when we create an order, it has items in the order; it has the quantity of those items.0320

And then also, the order involves a customer submitting their name and shipping information.0327

We also just found out that, as we had in our email, we have had a timestamp for our particular order.0333

So, there is a bunch of different information, and we are going to see that, as in our Departments, it doesn't necessarily fit all into one table in our database.0339

If we look at a couple of ways to modeling it...0348

Let's say we had a table called Orders in our database: maybe we store an order ID;0350

we would store the date and time of the order; and then, we could store information, like the customer that ordered it:0360

we would store their name, and let's just simplify--rather than putting all of the different columns we have,0370

like city, state, and address, let's just say address.0376

And then, maybe we would have item 1, and then you could list the item number, and then item 1 quantity, item 2, and so on.0381

And so, this could be the structure of our Orders table.0399

Now hopefully, you will remember from our lesson where we talked about linking tables and departments0408

that this is not normalized data, in that, if we have a column for every item that we want to add to a particular order,0413

then it limits the number of items we can have in a particular order to the number of columns in the table.0420

So, we constantly have to be updating the size of our Orders table to add columns for new items.0424

For example, maybe it would be order ID 1 (I'm just going to put 2011), blank, just some string...0430

maybe item 1 is 1001 and the quantity purchased was 2; maybe item 2 was 1003, and so forth.0438

Well, we know that that is a problem, and we can normalize that.0446

One thing that we are going to do with our order database is: we could change the structure a little bit.0450

So now, we would have two tables: we would have one called Orders, which would contain all of the information except for the items.0457

And then, what we can do is create another table called orders_items, which would be a linking table,0476

which we learned about in Departments, where we would link multiple items to a particular department.0482

And here, we could have two foreign keys that make up a primary key to the table.0486

We could have an orderID and an itemID.0493

For example, order 1--we could have an entry that says item 1001 was part of order 1.0497

For each additional item that is in order 1, you could add information.0504

Additionally, because we have a quantity associated with an item and order, we are going to have a quantity column.0508

So, we could say there were two 1001 items in order #1; there was 1 1002 item.0514

And so, now we have normalized our database, and we can add as many items to a particular order as we want,0523

without having to change the structure of our database.0529

Additionally, one other thing I will mention while we are here is: we are also going to add additional features to add a price column,0532

because as you know, at the store, sometimes the prices of items change, and so forth.0542

So, we would probably want to note in our database what the price of a particular item was when it was purchased.0547

So, maybe we would say that this one was priced at $50 at the time of the purchase; this one was priced at 45.0552

So, even if we had...maybe there was order #2 that had 1001; maybe they purchased 3 of them;0558

but there was a sale that day, so the price was down to $40.0565

Now, we can incorporate that information into our table.0569

So now, we have this order table that has information about the order, which is the customer information,0573

the date and time of the order, and its ID: and then we have another linking table that contains all the order information.0579

One other thing that we are going to add to our Orders table, just so you will be aware, is:0586

we are also going to add a tax rate column.0591

And the reason for that is: we are going to put the tax rate that was used when a particular order was made,0596

because the tax rate can change; the idea being that, when you look up an order in a database in the future,0600

you want to be able to figure out exactly what you charged for tax, how much each price was, and so forth.0605

Now, we could just create a column called total and store the total of the order;0612

but another thing with database design is that typically, you don't want to store anything that you can calculate,0618

because in a way, it is kind of redundant.0624

If we have all of the information about the prices of the items and the tax rate, we can calculate the total.0627

And therefore, we don't need to have that extra information in the database, because we can already generate it.0631

So, in that effort, we are going to include a tax rate column, as you will see in our Orders table.0640

Now, one thing that we can do is further normalize our database by moving customer data into its own table.0648

For example, we saw in the last version of the Orders table: each order had the orderID, the tax rate,0656

the date and time stamp, and then the name and address of each customer.0663

And so, if we flushed it all out and showed all the different columns for a customer, and so on,0667

we could see that we might have order 1 (just write down a fake date), 10% tax rate,0706

Joe Smith, who lives at 101 Main Street, and doesn't have an apartment number; and we could put a city and state.0713

Well, typically, hopefully, for our store, we want multiple customers to come back to the store.0722

So, we would like to have Joe be able to make more than one order.0728

So, let's say Joe makes another order, order #2 for Joe Smith.0731

He has the same address as before, so now we can see that we have redundant data in our tables again.0739

And as we learned in our lesson on linking tables, part of normalizing a database is to reduce redundant data,0745

because right now, we have all the information about Joe Smith and his customer address, listed in our table twice.0754

So, we have it taking up more space than we need to; we could separate it out and put it in one spot.0762

Additionally, if Joe changes his address, and we need to know that for the future--we want to update that on our orders--0768

we would have to go and update each individual row that Joe has.0775

Instead, what we can do is create a link to another table and extract that information out and put it into a Customers table.0779

So now, we can generate a primary key for our Customers table called custID0791

that will have all of that same information, except it will have this additional custID column.0808

So now, if Joe comes back to the store, we can update his address in one spot.0823

And what we can do is erase all of the customer data from this table.0828

And instead, we will include a foreign key column that is going to be a foreign key to this custID.0837

So, we are going to say custID=1.0842

So now, when we look up information about an order, we look at the Orders table.0853

We see that order 1 was ordered on this particular date, with this particular tax rate.0857

We can see that the customer that ordered it was a customer associated with ID number 1.0861

So then, we could look up the information in the Customers table and find out all of Joe's information.0867

And we could do that for every order that has custID #1.0872

Additionally, as we saw in the last page, all of the information about the items in a particular order is stored in our linking table,0875

where we have order #1, item 1001, quantity, price, and so forth.0883

We have all this information spread into three different tables: it seems more complicated, but it makes our database, in many ways, more efficient.0891

And the reason that we are bringing that up in this lesson is because now,0901

when we want to get information about an order (for example, we are going to be adding orders to our database),0905

for the purpose of our website administrator being able to go into the admin site and look up all the orders that have been placed;0909

well, in order to do that, we are pulling information from a number of different spots.0919

So, that is where the concept of a join comes in, which is where you can query data from multiple tables.0923

When we want to find information about order #1, not only do we want the information in the Orders table,0930

but we also want information about the customer, Joe Smith.0935

So, we are going to be creating a query that, in one result set, is going to pull information from the Orders table, as well as the Customers table.0938

Because there is only one customer associated with each order, we can do that in a single result set.0946

We can't do that with orders_items, so we are not going to be performing a join, as we will learn about, on the orders_items table,0952

because there are multiple items associated with each order, and there is not really a good way to do that with JOINS.0959

But for our customers, it is going to work out; and this is something that you will be commonly using,0967

as your databases get more complex, where you will be pulling data from multiple spots.0973

Now, I am just going to walk through the creation of the tables that we are going to have to represent orders in our database.0980

I have already logged into our MySQL Monitor.0987

So, let me create the table Orders; we are going to do a typical unsigned integer auto-increment for our primary key.0995

We are going to have a dateTimeOrdered column, which is going to be the date and time that an order was made.1018

That is going to be our new data type that we learned about, DATETIME.1023

We are going to have a tax rate column; it is going to be the tax rate that was used at the time the order was placed.1028

And for that, we are going to use a floating-point number that is going to be three digits long, all of which are after the decimal point.1036

So, we can have a three-digit percent amount (like .0975 would be 9.75%, and so forth).1047

And so, that is going to allow us to pull our tax rate.1064

And then, we are going to create a custID column, which is going to be a foreign key that is going to link to our Customers table.1068

And because we are going to be using an unsigned integer as the primary key for that table, we would list that as the same data type.1076

So, now we have created our table, which is going to look like this, with these four different columns.1084

We are going to create the Customers table to store the customer information, so that we can have multiple customers making multiple orders.1095

And then, we are just going to add all the different fields for our customer.1118

Now, we are just using variable-length strings for all of these different fields, because they are going to be able to vary--1152

except for, in particular, our state and our ZIP code: we know that the state is going to be two characters long,1164

and our ZIP code will be 5 characters long, so we are going to use CHAR data types for those.1169

And now, if we look at our Customers table, we can see that, if we remember our web application,1177

when a user submits information when completing an order, they put in their first name, their last name, and then their address.1187

And this is going to embody all of that information.1192

And then, finally, we just need to create that linking table that is going to link items to a particular order.1195

And we are going to have a double-column primary key, because an item can only be in an order one time, so that is a unique combination.1210

And we are going to give them the same data types that the primary keys have in their respective tables.1222

So, we have an orderID; itemID is going to correspond to the ID of an item in our store.1228

And then, as mentioned, we are going to have quantity, which will just be an integer,1239

and then price, which is the price of the item at the time of the purchase--we will have it be the same as the price column1244

in our Items table, which is a decimal of seven digits long, with two after the decimal point.1252

So now, we have our three tables set up that are going to be able to hold all the information about our orders.1265

And we can see here that we have...1272

Oh, and the one thing I forgot was: let me go ahead and get rid of...I forgot to assign a primary key.1275

I'm just going to redo this statement; and now, we can see that they are the primary keys.1295

So now, we have this set up to be able to add orders to our database.1317

What we are going to learn about now is how to query information from these multiple tables.1321

And what we are going to be doing, as part of the required homework for this lesson, is making the same setup1325

on your own home database, so that you will be able to use our new version of the web application.1331

That is going to give you additional practice with using CREATE TABLE statements.1335

And it is also going to give you a better understanding of how the tables are structured to work together.1339

A join, as implicitly mentioned, is a way to extract data from multiple tables in a single SELECT query.1348

For example, when we want to pull up an order, we want to pull information from a row in the Orders table;1359

and then, we also want to pull information about the customer associated with that order.1364

Now, we could do that in two separate queries: we could run a SELECT query on the Orders table,1369

and say, "Give me all the information about order #1"; then we could pull out of that data result set the ID1373

of the customer that is associated with that order, run a SELECT query on the Customers table, and then pull that information.1379

But JOINS allows us to do it all in one query, so we can have one result set that we can parse over.1384

The basic, most common type of join is called an inner join, and it uses the inner join SQL keywords.1390

And this is what a JOINS query looks like: it says, "Select all the columns from the Orders table that are joined with the Customers table."1399

Now, what this query means is: when you just have a query that is just like this, where you specify the name of one table,1410

and you specify that it is an inner join, that it is joined with the other table, the result set1419

is going to be a combination of all of the rows of the Orders table, combined with all of the rows from the Customers table.1425

For example, if the Orders table had five rows in it, and the Customers table had two rows in it,1431

our result set from this query here would be 10 rows, and it would be a combination1438

of each of the five orders, with each of the rows in our Customers table.1444

It is a little abstract, so let's look at a quick example.1450

If I create a table called Employees that is just going to have an ID for the person and a name,1455

and then we are going to link them to a particular department; we are going to use a foreign key from another table.1486

We are going to link them using a deptID, and let's go ahead and put someone in this table.1490

Move it up a little bit...1514

We have Joe Smith that is going to be in department #1, and then Jane Jones, who is going to be in department #2.1541

And just to look at what our Departments table looks like, if you don't remember: we have three departments1547

in our default setup: Apparel, Electronics, and Sporting Goods.1554

So now, let's say we are going to show you what an inner join would look like if we join these two tables (Departments and Employees).1558

We do SELECT, and we are going to select all of the columns from Employees, and we are going to join that table with the Departments table.1568

And as you can see, we have six rows in our resulting table, because we have three rows in our Departments table and two rows in our Employees table.1583

And what that inner join does is creates a giant table that matches each row in the Employees table with each row in the Departments table.1593

So, we can see that Joe Smith (actually, let's do this) is one entry in our Employees table.1604

But he is matched up with each different department, so there are three rows here:1625

one matching Joe Smith up with department 1; one matching Joe Smith with department 3, department 2,1629

and so forth; and the same thing for Jane Jones.1633

And so, that is what an inner join is: it creates a combination of all the rows in all the different tables.1636

This particular case is not very useful, but what we are going to learn in the next slide1643

is about something called the join condition, that is going to allow us to define information like...1646

typically, what we would want to find out would be information about Joe and the department he is in.1652

And so, what we can do is: out of this table of information, this Join table, we can restrict the rows that show up.1657

For example, we would only want the row where the deptID in the Employees table matches the deptID in the Departments table.1664

And that would give us, say, in our same query...if we ran that query, we would be able to get1674

the information about Joe Smith and the name of the department he is in.1678

We don't really care that he is matched up with the row that matches him up with department 3, number 2.1683

We want it where they are the same; so, we are going to learn about that in the next slide.1687

A join condition is pretty much like a WHERE condition; it specifies which rows you want from a result set.1696

We learned that the inner join has a giant table result set; it combines all of the rows from all of the different tables into a giant table.1703

And what we can do is: we had the same query as before, SELECT * FROM Orders, inner join, Customers.1712

We can add what is known as a join condition, that is specified by the keyword ON.1719

And what we are going to say for this particular join would be, "After you combine all of the rows of the Orders table1724

with all of the rows of the Customers table, only give me the rows1732

where the custID of the Orders table matches the custID of the Customers table."1736

And that comes up because, in our Orders table, this is the order ID, the tax rate, the date...and then, we have a customer ID, for example, custID 2.1744

So, if we did a join on both the Orders and Customers table, it would pull up a table that--1760

if there were five different customers, it would have information about each customer associated with this order.1766

So, it would say order 1, and it would match it up with customer 2, and customer 1, and so forth.1774

Well, we are interested in where the customer ID is matched up.1783

So, in this join condition, we get rid of this row from that join.1786

And it would say, "Only give me the rows where the customer ID's of those two tables match up,"1792

because that is what we use to link the information from those two tables together.1796

Because JOINS queries involve multiple tables, you need to be able to specify specific columns from specific tables.1801

And that is what we have done up here.1809

And the syntax for that is: you specify the table name, followed by the period, followed by the column name.1810

And so, that is what we used in our clause here, where we said the custID column of the Orders table is equal to the custID column of the Customers table.1816

And they work the same way that a WHERE clause does: you can have AND statements; you can have OR statements.1824

So, you can add multiple conditions that further define which rows you would like to receive.1831

For example, to perform a more practical, useful query, using a join on this Employees table and Departments table1836

that we just set up, we are going to say, "Select all of the columns from the Employees table and the Departments table;1845

join them together," so we have this giant table; and then, we are going to specify a condition.1851

And we are going to say, "Only give us the rows where the deptID column of the Employees table matches the deptID of the Departments table."1858

And when we run that, we are going to see that we get two rows.1875

We only have two employees; each one is only assigned to one department.1878

So, as you can see, what it has done is pulled the information from the two different tables.1883

You can see that, from the Employees table, it pulled the employee ID, the name, and the department ID that it is in.1889

And then, because we specified a star here, it pulls all of the columns from the Departments table, as well.1896

We have the department ID and the name from the Departments table.1902

And you can see that these two department ID's match up; the same thing for Jane Jones.1906

So, that is how we would pull information together.1909

And that is how, in our web application, we are going to pull information from the Orders table, and then also pull information from the Customers table.1912

Now, one thing you will notice here is: because we have used the star...1920

that star implies, "Give me all the columns from the first table (Employees), as well as all the columns from the second table";1924

because they have this matching column, deptID, it shows up twice, because they both have it.1930

And we are not necessarily interested in that; we are just interested in the other information--1938

for example, what might be related, or might just be that we only want to see it once.1941

So, what we can do is change our SELECT query.1945

And instead of specifying all of the columns; whereas before, we know that we could specify, for example, name,1951

and that would give us the name of the Employees table; well, because we are using multiple tables now,1956

we are going to specify the table name, followed by the column name that we want.1961

So, let's say we want the name of the employee from the Employees table, and the name of the department from the Departments table.1968

I'll try to get this...it doesn't look like it is fitting on one line.1986

What this is saying is, "Give me the name column of the Employees table and the name column from the Departments table..."1989

"from the join of the Employees and Departments table, where employees.deptID equals departments.deptID."1996

And so, now we have the name of Joe Smith, who is in the Apparel department, and Jane Jones in Electronics.2016

So, that is another way that we can use this table name/column name syntax within our query.2022

Not only can we use it in a join condition, but we can also use it to specify what columns we want to get.2027

And so, that query made use of both an ON condition, as well as specifying what columns we want.2035

Now, one other thing that we could do is: typically, when we are doing this,2042

we also want to only maybe pull up the particular user.2047

We want to find out what department Joe Smith is in.2050

So, if we know Joe Smith has employee ID number 1, we can also add an AND clause to our join condition,2052

that says, "Also, only give me the rows where, in the Employees table, the employee ID equals 1."2060

And that is going to pull up Joe.2071

And so, as you can see, these queries are getting more and more complex.2072

But it allows you to pull a lot of information together.2076

We learned the explicit method of specifying a join between tables.2083

There is also something known as an implicit join, which is just a simpler syntax for specifying2087

that you want to inner join two tables and pull data from both of them.2093

The way you do that is: you simply say SELECT; you list the columns you want; and then, you say FROM all the tables you want.2097

So, when you say FROM, and then you list two tables separated by a comma, that is going to perform an inner join on those tables.2105

So, that would be the same thing as if we had written FROM Orders inner join Customers.2111

This right here is the same thing as this; it is a little less syntax-y with advanced words--it is a little more intuitive.2137

And then, instead of an ON clause, we can specify a WHERE clause, which is the same as our join condition,2146

where we had ON orders.custID equal customers.custID; we now do that with fewer words.2151

For example, this same query that we just ran, where we pull the employee's name and department's name,2159

and we have Employees joined with Departments, and we get that join of both of those two tables...2173

Actually, let's add a join condition, so that it limits the results.2181

We have our two different rows here.2193

So, we could write this query right here that is an explicit join.2195

We have explicitly specified we want a join between Departments and Employees.2199

Just another format--another syntax for doing that--would be...2203

And by separating the table names we want to join by comma, we are specifying that we want those joined.2214

And then, we are just saying, "And then, from that join, give me all the rows where..."2220

And we can see that we are able to get the same information.2235

Implicit joins are just sometimes simpler, and it is more intuitive syntax for joining two different tables for a query.2239

One other thing we are going to learn before the end of the lesson, that is related to using joins, is something known as aliases.2250

As you can see, our queries are starting to get longer and longer and more complex.2257

One of the things that SQL allows you to do is specify aliases, which are, in a way, kind of like a variable name,2262

or a shortcut--a shorter name to refer to a specific table.2268

For example, we could say, if we wanted the order ID, the first name, and the last name of our customer,2273

from the Orders table that is joined with the Customers table, where the custID of the Orders table2281

equals the custID of the Customers table, what we can do is: when we list Orders, and we list the table,2286

we can use an AS clause afterwards, and say AS o.2292

So, what this is saying is, "Select these columns from the Orders table, which, in this particular query, has an alias that can be used for it, which is o."2296

So, this is the same thing, right here, as Orders.orderID.2306

And what it does is that the less typing that we have to do makes our queries a little simpler.2314

And we can do the same thing for the Customers table: we are going to say, "We are going to refer to the Customers table within this query as c."2318

So, this is going to say, "Get the firstName column from the Customers table and the lastName column from the Customers table."2323

And this is going to say that the join condition is going to be "where the custID of the Orders table equals the custID of the Customers table."2328

So again, looking back at our other query that, as you can see here, was rather long, we can go ahead and change it a little bit.2337

We are going to call our Employees table e; so I am going to give you the name from the Employees table.2347

We are going to call our Departments table d and give you the name from the Departments table.2351

Actually, let's do a regular integer...2363

So, we are going to use e as the alias for our Employees table, and we are going to be able to pull up the same data.2368

Now, you might say, "Well, that query is almost as long as the other one is."2391

But let's say that we have 10 different columns we are pulling from a particular table; that is significantly going to shorten the length of the query.2395

Also, we might have many more join conditions.2404

For example, if we add one more join condition where the employee ID equals 1, so we are just looking up Joe,2406

that is another spot where we can use it, as well.2422

And so, it is just going to significantly shorten the queries, in a way to make them a little bit easier to read and write.2424

And so, that is the purpose of aliases.2430

For the required homework, I am going to have you create the three tables that we are going to need2434

to use orders in our database, the ones that we learned about in this lesson.2443

You are going to create a table called Orders, just like we did here.2446

It is going to have four columns: orderID, dateTimeOrdered, taxRate, and custID (which is a foreign key that is going to point to a row in our Customers table).2448

So, we can have multiple customers linked to multiple orders.2458

You are going to create the Customers table, just as we had done.2462

It is going to have a primary key column, which is custID, which is what we are going to use to link an order to a customer.2465

It is going to have all of this different fields containing information about a particular customer.2471

And then finally, we are going to have our orders_items linking table, which is going to allow us to link multiple items to a single order.2478

And so, the primary key of the table is going to be a combination of the orderID column and itemID, which, in this particular table, are foreign keys.2485

The orderID is a foreign key, because it refers to a primary key of the Orders table.2493

The itemID is a foreign key, because it refers to a primary key of the Items table.2500

So that way, we can uniquely link a unique item row to a unique order row.2504

And then, we are also going to specify the quantity, which is going to specify the quantity of this particular item in this particular order,2510

and also the price of this particular item when this particular order was placed.2516

And so, when you go ahead and set that up and run these CREATE TABLE statements,2522

you will be able to have your database set up for the next web application version that we are going to be going through.2526

That ends today's lesson; thank you for watching Educator.com, and I look forward to seeing you next time.2531