2.21 Cross Join
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
Already have an account? Sign In »
7 hours 36 minutes
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.
There's no vent diagram to show you visually what this relationship looks like
and conception Aly. It's not too bad to explain
what it is is. We take every row from Table A and we connected to every Roman table B
and we returned. That is the data set.
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.
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.
And if you do a cross joint on two tables of that size, well, you're gonna get a result set that is
a 1,000,000 times a 1,000,000.
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.
If you're really trying to do a
now that said,
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
to kind of show off when you might use this type of joint.
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,
you could also just fall along because there isn't too much coating going on in this lesson.
So for this quarry, what we're going to do as we're going to take a
sales table. So on the sales person table
employees are identified of whether or not they represent a sales person.
So perhaps we want to show
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.
So we need to build a data set that builds a
that shows every sales person
and every financial period.
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
and we have a table that is every financial period.
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.
So we're going to the sequel editor, new sequel editor,
and we're going to go ahead and start typing Select. I don't know what I want. Someone dropped down the next line,
and I'm going to do the sales person first. I'm going to give it an alias of sales,
and then I'm going to do the fin period set up table
would give that Daly is a fen.
we do need to make one joint
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
the servicing company to set up multiple companies if desired.
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.
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.
so we're just insurance that are Cartesian product remains in the same company,
there are choices here. Salesperson CD. Okay, that's the idea that people see in the front end.
It was safe. N dot period number
and fen dot
friend dot and date. Let's take another look at that table real quickly.
Period Number, description, description. Let's bring back the description to
put it right there. Don't forget your commas. You need your commas.
Won't work without that
descriptions. Gonna be the month name it looks like.
So let's go out and run this
and let's go and sort it so because right now it's cycling through
each sales person CD so C C 1 to 10 and then it resets.
So we've sorted by the salesperson CD with Nd beaver
That's independent of the secret query,
and we can see that we are seeing
sales person one represented in each financial period.
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
and run the same quarry. And because company choose the only company that exist,
it'll be fine. We'll get the exact same results
on there we go again.
So that is the cross joint.
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.
that finishes up this lesson. I hope to see you in the next lesson. Thank you.