Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back to module to databases. This is less than four sequel joints, and we're in the last sub lesson 4.8 to cross join. The Cross Joint is also known as a Cartesian product.
00:12
There's no vent diagram to show you visually what this relationship looks like
00:17
and conception Aly. It's not too bad to explain
00:22
what it is is. We take every row from Table A and we connected to every Roman table B
00:28
and we returned. That is the data set.
00:30
So if you had four rows and Tim play and four rows and table be, you would get a data set that was 16 rose long.
00:37
Now this type of joint can be considered dangerous if applied in the rock situation. It is common to find tables and production databases that can have millions or billions of records.
00:48
And if you do a cross joint on two tables of that size, well, you're gonna get a result set that is
00:55
a 1,000,000 times a 1,000,000.
00:57
And that's a large number. And it would take any database a little bit of time to build that results set and depending on how large that results that is, it could drag your databases performance, and users may notice that the database is running slowly. So it is something to consider when working with large tables.
01:15
If you're really trying to do a
01:18
cross joint
01:19
now that said,
01:21
I'm gonna show you an example that uses the axiomatic a test at a base. Unfortunately, the other two tests databases don't really have a good table that kind of
01:30
to kind of show off when you might use this type of joint.
01:34
Now, if you don't have this database installed, I install it in ah, later on in the lesson. So if you want to follow along, go ahead and jump to that installation and then come back. Once you've finished the installation part,
01:45
you could also just fall along because there isn't too much coating going on in this lesson.
01:51
So for this quarry, what we're going to do as we're going to take a
01:56
sales table. So on the sales person table
02:00
employees are identified of whether or not they represent a sales person.
02:06
So perhaps we want to show
02:09
every sales person in every financial period that the company has. So this application also has offend period set up table where the user's define every financial period within the application.
02:23
So we need to build a data set that builds a
02:27
results set
02:29
that shows every sales person
02:30
and every financial period.
02:34
So that sounds kind of like a Cartesian product if you think about it or the cross joint, because we need to show every sales person in every financial period
02:42
and we have a table that is every financial period.
02:46
So with that said, let's go ahead and start coating that joint, so make sure you have the database active if you're following along.
02:53
So we're going to the sequel editor, new sequel editor,
02:57
and we're going to go ahead and start typing Select. I don't know what I want. Someone dropped down the next line,
03:02
and I'm going to do the sales person first. I'm going to give it an alias of sales,
03:07
and then I'm going to do the fin period set up table
03:12
would give that Daly is a fen.
03:14
Now
03:15
we do need to make one joint
03:19
in this table just to make sure and be safe that we are in the same company. A lot of applications allow the client or the
03:29
the servicing company to set up multiple companies if desired.
03:32
So if we had a company to that might have financial peers, they're different from Company three and so on. So let's go ahead and make that connection.
03:42
You can, of course, leave this out if you want to and you'll still get a cross joint. Were just being a little more restrictive on our cross joint. But we're still doing a cross joint.
03:55
Yeah, okay,
03:58
so we're just insurance that are Cartesian product remains in the same company,
04:04
so sales
04:06
dot
04:09
account CD
04:13
sales that
04:15
there are choices here. Salesperson CD. Okay, that's the idea that people see in the front end.
04:20
It was safe. N dot period number
04:25
and fen dot
04:29
start date,
04:30
friend dot and date. Let's take another look at that table real quickly.
04:38
Period Number, description, description. Let's bring back the description to
04:43
put it right there. Don't forget your commas. You need your commas.
04:46
Won't work without that
04:47
descriptions. Gonna be the month name it looks like.
04:49
So let's go out and run this
04:54
and let's go and sort it so because right now it's cycling through
04:59
each sales person CD so C C 1 to 10 and then it resets.
05:05
So we've sorted by the salesperson CD with Nd beaver
05:10
That's independent of the secret query,
05:12
and we can see that we are seeing
05:15
sales person one represented in each financial period.
05:20
Now, let's say for some reason we didn't care about the company I d. All companies share the same financial periods. Well, we just get rid of this
05:30
and run the same quarry. And because company choose the only company that exist,
05:33
it'll be fine. We'll get the exact same results
05:39
on there we go again.
05:42
So that is the cross joint.
05:44
I hope you got it working. If you were following along, and I hope you understood what we were doing here again, we were just taken each sales person in a sales person table, joining it to a financial period table and saying, OK, I want representation in every financial period. So I want every record that's in that table B.
06:00
And, uh,
06:01
that finishes up this lesson. I hope to see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor