2.21 Cross Join

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,