Original subtitles

0 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.