Introduction

In this article, we are going to see how a CROSS JOIN works, and we will also make use of this SQL join type to build a poker card game.

Database table model

For our poker card game application, we have created the ranks and suits database tables:

The ranks table defines the ranking of cards, as well as the name and symbol used for each card rank:

| name  | symbol | rank_value |
|-------|--------|------------|
| Ace   | A      | 14         |
| King  | K      | 13         |
| Queen | Q      | 12         |
| Jack  | J      | 11         |
| Ten   | 10     | 10         |
| Nine  | 9      |  9         |

The suits table describes the four possible categories used by the French playing cards:

| name    | symbol |
|---------|--------|
| Club    | ♣      |
| Diamond | ♦      |
| Heart   | ♥      |
| Spade   | ♠      |

Cartesian product

In the set theory, the Cartesian product of two sets (e.g., A and B), denoted by the A × B notation, is the set of all ordered pairs (e.g., a and b) where a is from the A set and b is from the B set. Basically, the Cartesian product represents all possible permutations of a and b pairs from two given sets of data.

In our case, the poker game playing cards are represented by the Cartesian product of our ranks and suits database tables.

SQL CROSS JOIN

SQL defines two ways of generating a Cartesian product:

• SQL:92, CROSS JOIN syntax
• SQL:89, Theta-style syntax

SQL:92 CROSS JOIN

The preferred way to generate a Cartesian product is to use the SQL:92 CROSS JOIN syntax.

In our case, to generate all possible poker cards, we can use the following CROSS JOIN query:

SELECT
   r.symbol AS card_rank,
   s.symbol AS card_suit
FROM
   ranks r
CROSS JOIN
   suits s

When executing the SQL query above, the database will generate all possible permutations of ranks and suits pairs, giving us the poker game deck of cards:

| card_rank | card_suit |
|-----------|-----------|
| A         | ♣         |
| A         | ♦         |
| A         | ♥         |
| A         | ♠         |
| K         | ♣         |
| K         | ♦         |
| K         | ♥         |
| K         | ♠         |
| Q         | ♣         |
| Q         | ♦         |
| Q         | ♥         |
| Q         | ♠         |
| J         | ♣         |
| J         | ♦         |
| J         | ♥         |
| J         | ♠         |
| 10        | ♣         |
| 10        | ♦         |
| 10        | ♥         |
| 10        | ♠         |
| 9         | ♣         |
| 9         | ♦         |
| 9         | ♥         |
| 9         | ♠         |

Theta-style join

Prior to the SQL:92 standard, joins could only be expressed via the theta-style syntax, which requires the FROM clause to list all tables that need to be joined. To generate a Cartesian product, the WHERE clause can simply omit filtering the result set produced by executing the FROM clause operation.

In our case, to generate all possible poker cards, we can also use the following theta-style join query:

SELECT 
   r.symbol AS card_rank, 
   s.symbol AS card_suit 
FROM 
   ranks r, 
   suits s

Although you can use the theta-style join to generate a Cartesian product, it’s recommended to use the SQL:92 CROSS JOIN syntax.

Poker game

Now that we know how to generate the poker game deck of cards, we need a way to generate random hands for each player. To do that, we need a way to randomize the deck of cards.

As I explained in this article, depending on the underlying database system, you can randomize a given result set using either DBMS_RANDOM.VALUE (e.g., Oracle), NEWID() (e.g., SQL Server), random() (e.g., PostgreSQL), RAND() (e.g., MySQL).

After we randomize the result set, we must extract 5 cards for each player, so we need to use a Top-N clause on the randomized deck of cards.

All this can be done using the following JPQL query:

List<Card> cards = entityManager
.createQuery(
    "select new Card(r.symbol, s.symbol) " +
    "from " +
    "   Rank r, " +
    "   Suit s " +
    "order by " +
    "   random()", Card.class
)
.setMaxResults(
    playerCount * POKER_HAND_CARD_COUNT
)
.getResultList();

While the JPQL uses the theta-style syntax, the underlying SQL query generated by Hibernate is going to use the SQL:92 CROSS JOIN instead. In our case, we just have to include both Rank and Suit entities in the from clause of the JPQL query, and Hibernate will use a CROSS JOIN between the associated ranks and suits database tables.

Because Java Persistence does not define a random() function, we can easily add that using the Hibernate MetadataBuilderContributor, as follows:

private String randomFunctionName = "random";

@Override
protected void additionalProperties(
        Properties properties) {
    switch (database()) {
        case ORACLE:
            randomFunctionName = "DBMS_RANDOM.VALUE";
            break;
        case SQLSERVER:
            randomFunctionName = "NEWID";
            break;
        case MYSQL:
            randomFunctionName = "rand";
            break;
    }

    properties.put(
        "hibernate.metadata_builder_contributor",
        (MetadataBuilderContributor) metadataBuilder -> 
            metadataBuilder.applySqlFunction(
                "random",
                new StandardSQLFunction(randomFunctionName)
            )
    );
}

Now, the random() JPQL function will default to random() unless Oracle, SQL Server, or MySQL are being used.

Notice that the previous JPQL query did not specify the fully-qualified name of the Card DTO we are using to store the result set. This is because we are using the ClassImportIntegrator provided by the hibernate-types project, as explained in this article.

The Card DTO is designed to hold the rank and suit generated by the CROSS JOIN of the ranks and suits tables. The Card class looks like this:

public class Card {

    private String rank;

    private String suit;

    public Card(
            String rank, 
            String suit) {
        this.rank = rank;
        this.suit = suit;
    }

    public String getRank() {
        return rank;
    }

    public String getSuit() {
        return suit;
    }

    @Override
    public String toString() {
        return rank + suit;
    }
}

The last thing to explain for our JPQL projection query is the use of setMaxResults method. The value we passed to the setMaxResults method represents the size of the result set, which in our case is given by multiplying the number of players with the number of cards in a poker hand (e.g., five cards for each player).

For more details about limiting a SQL query result set to the first Top-N records, check out this article.

Testing time

With the List of Card objects that were generated randomly, we just have to assign the poker hands to each player:

for(int i = 0; i < playerCount; i++) {
    int offset = i * POKER_HAND_CARD_COUNT;
    
    LOGGER.info(
        "Player {} cards: {}",
        i + 1,
        cards.subList(
            offset, 
            offset + POKER_HAND_CARD_COUNT
        )
    );
}

Now, assuming the playerCount variable has the value of 4, let’s see how the randomized result set produced by CROSS JOIN will work on various relational database systems.

Oracle

When running this poker hame on Oracle, the following CROSS JOIN query is executed:

SELECT 
    r.symbol AS col_0_0_,
    s.symbol AS col_1_0_
FROM 
    ranks r
CROSS JOIN 
    suits s
ORDER BY 
    DBMS_RANDOM.VALUE() 
FETCH FIRST 20 ROWS ONLY

-- Player 1 cards: [ J♣, A♦, 10♠,  9♥,  Q♠]
-- Player 2 cards: [ J♥, J♦,  K♦,  K♠,  A♥]
-- Player 3 cards: [10♥, 9♣,  A♣,  Q♣,  A♠]
-- Player 4 cards: [ Q♥, K♣,  Q♦, 10♣, 10♦]

The CROSS JOIN between ranks and suits generates the Cartesian product. Afterward, the ORDER BY clause will randomize the result set, and the SQL:2008 FETCH FIRST 20 ROWS ONLY query limit syntax will limit the size of the result set.

SQL Server

When running this poker hame on SQL Server, the generated SQL query is almost identical to the one executed on Oracle, the only exception being the result set randomizing function:

SELECT 
    r.symbol AS col_0_0_,
    s.symbol AS col_1_0_
FROM 
    ranks r
CROSS JOIN 
    suits s
ORDER BY 
    NEWID() 
FETCH FIRST 20 ROWS ONLY

-- Player 1 cards: [J♠,  Q♦, A♣, A♦,  A♥]
-- Player 2 cards: [K♠,  Q♠, Q♣, 9♥,  A♠]
-- Player 3 cards: [9♣, 10♦, J♥, K♥, 10♥]
-- Player 4 cards: [9♦,  Q♥, K♦, J♣, 10♣]

PostgreSQL

When running this poker hame on PostgreSQL, the executed SQL query uses the CROSS JOIN syntax as well. The result set is restricted using the LIMIT clause this time, although FETCH FIRST 20 ROWS ONLY would also work fine on PostgreSQL:

SELECT 
    r.symbol AS col_0_0_,
    s.symbol AS col_1_0_
FROM 
    ranks r
CROSS JOIN 
    suits s
ORDER BY 
    random()
LIMIT 20

-- Player 1 cards: [K♥, K♦,  Q♠, 9♥,  A♥]
-- Player 2 cards: [9♣, A♦,  J♦, K♣,  A♣]
-- Player 3 cards: [J♣, A♠,  Q♦, 9♠,  Q♥]
-- Player 4 cards: [K♠, J♥, 10♦, 10♣, Q♣]

MySQL

When running this poker hame on MySQL, the executed SQL query also uses the CROSS JOIN syntax as well as the LIMIT clause since MySQL does not support the FETCH FIRST 20 ROWS ONLY SQL:2008 syntax yet:

SELECT 
    r.symbol AS col_0_0_,
    s.symbol AS col_1_0_
FROM 
    ranks r
CROSS JOIN 
    suits s
ORDER BY 
    rand()
LIMIT 20

-- Player 1 cards: [ J♣, K♦, A♣,  K♣, Q♣]
-- Player 2 cards: [10♣, Q♠, K♠,  Q♦, J♥]
-- Player 3 cards: [ J♦, 9♦, A♠, 10♦, A♦]
-- Player 4 cards: [10♥, 9♥, K♥, 10♠, 9♣]

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The SQL CROSS JOIN allows you to generate a Cartesian product for two given sets of data. When the underlying use case calls for generating a Cartesian product like it was the case for our poker game, then using a CROSS JOIN is the idiomatic way for addressing this task.

Note that a Cartesian product could be generated unintentionally, too, in which case it would indicate a flaw in the join conditions. For instance, this could happen when joining two or more unrelated one-to-many table relationships. For more details about this unintentional Cartesian product issues and how you can fix them, check out this article.

Insert details about how the information is going to be processed

DOWNLOAD NOW