All language subtitles for 8. Cross Join

So let us now discuss the cross join. Cross join gives us the Cartesian product between two sets
1

of data.
2

What is the cartesian product?
3

So it takes both the tables, So every row in table a, it will map all the values in table b
4

then again, it will go to the next row of table a and it will map
5

All the rows of table b.
6

So for all values in table a, it will map all values in table b.
7

So when we will discuss an example, it'll become more clear to you.
8

This join does not have the same syntax as other joins.
9

in other join we were writing as inner join, outer join left and right join.
10

This is just mentioning the two table names from table one and table 2.
11

And if there are other tables, also then table three, four and so on.
12

So the syntax is select, table name.column name and so on from table name and the other names.
13

As an example for crossjoin, I have created two different tables.
14

One is the year values table.
15

This table contains some set of years and the other is the month values table, which contains the 12
16

months of a year.
17

What I'm doing by cross joining these two tables is when I do a join for each year, I'll get all the
18

12 months mapped against it.
19

So in the result set I get for every year, there'll be 12 months.
20

So this will give me the total ResultSet of every month mapped against every year.
21

So I have created this table of month values and year values.
22

Using the create table function. Create table
23

command, then, I inserted 12 values of the month in the month values table using the insert value command
24

Then I have entered.
25

Nine years in the year values, using the insert into command, if you run the the select star on month
26

values, you will see the 12 months.
27

If you run select start on year values, you will see nine years.
28

Suppose in a table you want each year and in each year you want to map the 12 months, it will take 12
29

into 9 rows.
30

So you would have to enter values into 10 rows.
31

Instead of doing that, we entered value into only 12 plus nine.
32

rows which is 21.
33

And we then write the cross join. Let us write cross join after the cross join we will get those
34

108 rows.
35

So a.b yyy column and the b.month column.
36

So here a will be our year table
37

and b will be the month table.
38

I think overall, this example is covering a lot of the other commands that we have discussed so far.
39

So this will be a revision for you.
40

We are creating a table inserting values, then using aliases and now we are also doing cross join
41

When I ran this query, you see, for 2011, I have only 12 months mapped for 2012
42

I have all the 12 months mapped.
43

So this basically has 108 rows, if you had to create this manually, you would have to
44

enter a lot of values, so this cross join helps you there
45

Similarly, if you want dates also plotted against this, you can create another table with dates into
46

it and then do a cross join with some conditions attached to it. Cross join basically help to get this repetitive
47

data easily.
48

So we have covered all the joins.
49

Apart from this, since we are discussing, merging and joining, two tables will need to cover two
50

more commands.
51

One is the Except command and the other is the union command.
52

So let us discuss the except command in the next video.