Yogesh Chauhan's Blog

CROSS JOIN in Postgres

in Postgres on October 19, 2020

When you think of CROSS JOIN, think about Cartesian product. Because CROSS JOIN will give us a result set which is the number of rows in the first table multiplied by the number of rows in the second table.

Kind of like this.

CROSS JOIN in PostgreSQL

Syntax:


SELECT a.column2, b.column2
FROM table a
CROSS JOIN table b;

I am using alias as well in the syntax. You can use name of the table instead of alias. Alias makes it better to write the query. Learn more about alias in this post:

Column And Table Alias In Postgres

CROSS JOIN Examples

I am using the database for all examples. It is available on my Github public repo:


SELECT c.contact_name, o.order_date 
FROM customers c CROSS JOIN orders o;

//Output
contact_name.        order_date 
"Maria Anders"	     "1996-07-04"
"Ana Trujillo"	     "1996-07-04"
"Antonio Moreno"	 "1996-07-04"
...
...

Now, if we have small tables (with fewer rows), the result set will be easier to look at. We have two big tables here so our result set has thousands of rows.

But I just wanted to show what CROSS JOIN is so included the example. 

Use my database from Github link and play with the data. Let me know if you have any questions.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
RBV Framework and closing of big brandsMiscellaneousWhat is IPS(Intrusion Prevention System), How Does It Work and What are the Detection Types?MiscellaneousA simple example on grid ‘auto-fill’ vs ‘auto-fit’CSSHow to Pop an Alert Box in PHP?JavaScriptWhat is Hoisting in JavaScript?JavaScriptHow to get Current Year, Month and Date in JavaScript?JavaScript