Cross Join

Hi,

today’s article is about Cross Joins and the few cases where you can use it.

In general a cross join just takes all rows from table A and combines them with all rows from table B. Other terms for cross join are cross product and cartesian product. Let’s make a small example. We need two tables:

TBL_A

TBL_B

Now you can do the CROSS JOIN with the following SQL:

 SELECT * FROM TBL_A CROSS JOIN TBL_B

Compared with an inner join you write CROSS JOIN and you leave the ON clause. That one is not needed as you don’t define any joining condition. That is because all rows from A are taken and combined with all rows of B. The result looks like this:

As you can see the row one from table A (1 / A) is taken and combined with all three rows from table B. The result are three rows where the first two columns (that come from table A) are the same and the last two columns (that come from table b) are changing. The same is repeated with row two from table A (2 / A).

So you can calculate the number of resulting rows with  rows in table A * rows in table B. In our example it’s 2*3 = 6 rows.

This type of join is seldom used as it makes sense only in a few cases. For example if you have a value per week and you want to distribute it on each day of the week. I have another table for that:

Now we run the following query:

 SELECT WEEK_START_DATE+DAY_OF_WEEK AS DAY_DATE,             WEEK_VALUE FROM TBL_VALUE_WEEK     CROSS JOIN (                         SELECT LEVEL-1 AS DAY_OF_WEEK                         FROM dual                         CONNECT BY LEVEL<=7                        ) week_days ORDER BY DAY_DATE

And the result looks like this:

As you can see we have all seven days of the week starting with 2017-12-04 and each day has the WEEK_VALUE of this week. Then comes the next week.

Let’s take a closer look at the query. We have a subquery which is needed for generating the seven days of a week. This is database specific. In this case it is an opportunity on an Oracle database. You could also create a table with the numbers 0 to 7 and work with that instead of a subquery. If you are working on other databases you have to change that part.

Besides the subquery we are just accessing the weekly table cross joining it so in a result we get seven rows a week instead of one.

If you are interested in further information on SQL just check out my BLOG or take a look at my new SQL book. I’ve added a free excerpt on this BLOG as well. You can also attend training on my corporate homepage. At the moment my classroom trainings are only available in German. Inhouse training and online courses are also available in English language. So check out my trainings (German) or my online courses (English)!

BLOG_0032_Cross_Join_Testdate_Examples.zip

New book: SQL Basics – Excerpt

Hi,

today I just want to give you an excerpt of my latest book SQL Basics – Learn SQL the easy way. The whole book is an introduction to relational databases and SQL. It starts with some theory like what is a database, relations between tables, normalisation, transactions, etc. After that the SQL queries are introduced starting with easier stuff like selecting columns, doing calculations, defining filters and sorting. After that multiple tables are queried with Joins (Inner, Outer, Cross) and data is aggregated and grouped. Subqueries are one of the more advanced topics. Furthermore you find set operations, manipulation of data, creation of database objects and an overview on analytic functions.

Though the code in the book is based on Oracle you can also use it if you work on MS SQL or MySQL databases. For that I’ve added an appendix with the differences between Oracle and each of the other databases. Furthermore you can download a training database with data, a tutorial for setting up a training environment based on oracle (I am planning to do the same for MS SQL and MySQL) and the solutions of the 80+ exercises. These solutions are downloadble for Oracle, MS SQL and MySQL. So you can compare the differences between the different databases.

You can download an excerpt, which consists of

• Chapter 1 – DB Theory
• Chapter 3 – Simple queries (except the excersies at the end)
• Chapter 4 – Queriing multiple tables (only Cross- & Inner Join)

You find the excerpt here: SQL Basics – Learn SQL the easy way – Excerpt

If you enjoy the book I will be happy if you buy it at Amazon. You can select between eBook or paperback. Just simply click on this link: http://a.co/eqnLdbh

If you prefer buying it in your own country-store and not at Amazon.com then you can click on country flags in the sidebar of my blog. You are directly routed to the book page in the specific country store.

Group By extensions III: GROUPING SETS

Hi,

again something on the GROUP BY extensions. Today we will deal with GROUPING SETS. Probably the most flexible and easiest way to create multiple GROUP BYs within one query. In part 1 of this series we discovered the ROLLUP clause, which let’s us automatically calculate totals along some kind of dimension, which is defined by the grouping columns. Part 2 described the CUBE clause which just simply calculate all possible sums based on the grouping columns.

GROUPING SETS let you exactly specify the totals you want to see in your query. If you just want a report total and some monthly totals then you can just define that and you get it. If you need all the totals for all levels of a dimension a ROLLUP might be easier, but in most cases GROUPING SETS will probably be.

Now let’s get to coding. The examples are based on the test data of part 2, i.e. the table looks something like this:

 Day Product No Product Name Turnover 01.01.2017 1 Product 1 20 2 Product 2 30 3 Product 3 40 02.01.2017 1 Product 1 30 2 Product 2 40 3 Product 3 50 …. … … … 30.04.2017 3 Product 3 330

In my first example I just want a monthly toal and a report total. With GROUPING SETS it looks like this:

 SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,             SUM(TURNOVER) AS TURNOVER FROM TBL_TEST GROUP BY GROUPING SETS( (to_char(DAY,’MM/YYYY‘)),                                                     ()                                            );

Result:

As you can see in this example you just put all the different GROUP BYs in different sets of brackets. All the columns that belong to a specific GROUP BY are put to one pair of brackets. The different GROUP BYs are then separated by comma. If a GROUP BY only consist of one column you could theoretically leave out the brackets. If you want a report total you leave the brackets empty.

Now another example with a monthly total, a report total and a total per month and product:
 SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,             PRODUCT_NO,             PRODUCT_NAME,             SUM(TURNOVER) AS TURNOVER FROM TBL_TEST GROUP BY GROUPING SETS( to_char(DAY,’MM/YYYY‘),                                             (),                                            (to_char(DAY,’MM/YYYY‘), PRODUCT_NO, PRODUCT_NAME)                                           ) ORDER BY MONTH, PRODUCT_NO NULLS LAST

Result:

In this example we have calculated three sums as you can see directly in the GROUPING SETS section of the SQL. This example could have been produced also with a ROLLUP if you change the clauses and leave out the first two rows of the GROUPING SETS section. As you can see often there are several ways to reach the target. For larger reports and if you need a lot of sums maybe ROLLUP makes it easier for you and the database. But sometimes you only need a few specific totals out of a lot of different combinations then maybe GROUPING SETS are easier to use.

The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the GROUP BY extensions.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

BLOG_0022_Testdata_Examples.zip

String Comparison Differences ( MySQL vs. Oracle)

When comparing strings in MySQL you find out that something is different compared with Oracle. Basicly said it’s that Oracle is case-sensitive when comparing strings and MySQL is not. Let’s say you have a table TBL_TEST that looks like this:

 TBL_TEST ID Name City 1 Schmidt kiel 2 Maier KIEL

Given the following SQL:

SELECT *
FROM  TBL_TEST
WHERE City=‘kiel‘;

In MySQL you receive both rows as the string comparison is not case-sensitive. In Oracle and MS SQL you would receive only the first row. It is the same if you use the LIKE operator.

So how will you accomplish a case sensitive comparison in MySQL then?

The trick is to use the operator binary in your equation. That converts a normal string into a binary string and the result is that the strings are compared by character code rather than by character. The SQL changes like this:

SELECT *
FROM  TBL_TEST
WHERE City=binary ‘kiel‘;

Now in MySQL you also only receive the first row. The binary operator can be placed on both sides, the effect is always the same. By having one binary string in your equation the strings are compared on byte level.

I hope this helps and explains this behaviour a little bit.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!