Questions tagged [cross-join]

CROSS JOIN is a join operation, that returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. Unlike other JOIN operators, it does not let you specify a join clause. You may, however, specify a WHERE clause in the SELECT statement.

CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. You may, however, specify a WHERE clause in the SELECT statement.

Examples

The following SELECT statements are equivalent:

SELECT *
FROM TEACHER
CROSS JOIN COURSE

SELECT *
FROM TEACHER, COURSE;

The following SELECT statements are equivalent:

SELECT *
FROM TEACHER
CROSS JOIN COURSE
WHERE TEACHER.ID = COURSE.TEACHER_ID

SELECT *
FROM TEACHER
INNER JOIN COURSE
ON TEACHER.ID = COURSE.TEACHER_ID

A CROSS JOIN operation can be replaced with an INNER JOIN where the join clause always evaluates to true (for example, 1=1). It can also be replaced with a sub-query. So equivalent queries would be:

SELECT * FROM TEACHER LEFT OUTER JOIN
COURSE INNER JOIN UNIVERSITY ON 1=1
ON TEACHER.ID = UNIVERSITIES.TEACHER_ID
WHERE UNIVERSITY.COUNTRY = 'US'

SELECT FROM TEACHER LEFT OUTER JOIN
(SELECT FROM COURSE, UNIVERSITY) S
ON TEACHER.ID = S.TEACHER_ID
WHERE S.COUNTRY = 'US'

Links

Cross Join in Oracle

530 questions
0
votes
1 answer

Microstrategy Output Level Error

I am doing my training in MicroStrategy and I was practicing creating filters with Output Levels with 3 attributes: Country, Product and Year, and one Metric Sum OrderQuantity from FactResellerSales with the metric as a report filter, with output…
Shreeya R
  • 1
  • 3
0
votes
3 answers

Duplicate Records in MySQL Geo Search Using Cross Join and Hversine Formula

I'm trying to complete a modification of this Google tutorial I've written this SQL to query a table of locations using the location "name." Given the name of the location, the query returns pizza restaurants within the proximity. To accomplish this…
Brandon Buster
  • 1,195
  • 1
  • 9
  • 12
0
votes
0 answers

mdx cross join without intersection

I've created market basket analysis cube and I'v tried to create query that return cross join of two hierarchies ( they are identical ) and gets the results without intersection. Unfortunately I can't get correct results. Problem: I have 2 sets: A…
0
votes
0 answers

SQL Server cross a view with a set based on hierarchy

I have a view in my SQL Server database which basically holds hierarchical information between some records based on id values of type int. A simple representation is as follows: ID Parent_ID 1 NULL 2 1 3 2 4 NULL 5 …
quantum
  • 3
  • 1
0
votes
2 answers

Subtracting 2 values from a query and sub-query using CROSS JOIN in SQL

I have a question that I'm having trouble answering. Find out what is the difference in number of invoices and total of invoiced products between May and June. One way of doing it is to use sub-queries: one for June and the other one for May, and…
fixnode
  • 97
  • 3
  • 8
  • 32
0
votes
1 answer

MySQL: Returning total record count from a CROSS JOINed row?

Here's my SQL Fiddle: http://www.sqlfiddle.com/#!2/672f4/1 I'm trying to get the total count of records for each name (technically, their cross-linked pid) that exists inside the table info. So, for example, the TOTAL column should read 3 for rob,…
daveycroqet
  • 2,667
  • 7
  • 35
  • 61
0
votes
3 answers

SQL Cross Join Query not working with variable

This cross join works fine: select * from x, y I am trying to run this query: select abc.col1 from abc (select * from x, y) abc But I get this error message: Msg 8156, Level 16, State 1, Line 2 The column 'col1' was specified multiple times for…
slayernoah
  • 4,382
  • 11
  • 42
  • 73
0
votes
2 answers

Select on a Cross Join Returning Wrong Results

****EDIT**** Adding SQL Fiddle Link HERE I created the scenario in SQL Fiddle to show what I am trying to accomplish. I found it very odd that it produces accurate results in SQL Fiddle yet produces the results below using my DB. However for the…
Kairan
  • 5,342
  • 27
  • 65
  • 104
0
votes
0 answers

Getting random profiles that have a match with current profile

I'm trying to get 3 random unique profiles with the same sex ID as the current user ID (orig.id_user = 6 in this example), and their respective reviews. SELECT DISTINCT u.id_user, s.review FROM user AS u CROSS JOIN user AS orig ON orig.id_sex =…
Andres SK
  • 10,779
  • 25
  • 90
  • 152
0
votes
2 answers

How to count many-to-many relations in postgresql

I have a table that looks like: a | b ---+--- 1 | a 2 | a 3 | a 1 | b 3 | b 2 | c 3 | c It represents many-to-many relatons a<->b. I'd like to get all existing relations a<->count(b)<->a, like: a1 | a2 | count ----+----+------- 1 | 2…
0
votes
2 answers

SQL - 3 table mash up (select all left, all right, show status of middle)

I'm stumped on this one. In short, I have 3 tables: Product, Ranging, Account. Ranging is the middle table which is the link between Product and Account. e.g. a ranging record links to 1 account and 1 product. Products can have many rangings, so can…
ALC
  • 55
  • 1
  • 1
  • 4
0
votes
2 answers

Self-Joins, Cross-Joins and Grouping

I've got a table of temperature samples over time from several sources and I want to find the minimum, maximum, and average temperatures across all sources at set time intervals. At first glance this is easily done like so: SELECT MIN(temp),…
pr1001
  • 21,727
  • 17
  • 79
  • 125
0
votes
2 answers

Is there any alternative to Cross Join in TSQL

I have two tables Table1 and Table2 where i need to find all the rows which satisfies the condition Fuzzy_Qgram(Approximate matching optimization) between Table1.LastName and Table2.LastName Here is my Query : Select * from Table1 Cross…
user42995
  • 335
  • 1
  • 5
  • 15
0
votes
2 answers

SQL script for special data representation

Please help me with my problem of data representation. I have some SQL script, that creates the following data presentation: Declare params cursor for Select distinct id_variable from [UIR_DB].[dbo].[Variable_Values] open params Declare @idparam…
George Pirkulov
  • 83
  • 4
  • 11
0
votes
3 answers

Combine 2 tables to join to

I'm using Postgres 9.1 and I have a query which returns 2 columns which I would like replicated even if no data is present. The important parts of my query are SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...) WHERE…
Jon
  • 3,985
  • 7
  • 48
  • 80