SQL Exercise: Customers from a different city than the salesmen

SQL JOINS: Exercise-20 with Solution

Write a SQL statement to make a Cartesian product between salesman and customer i.e. each salesman will appear for all customers and vice versa for those salesmen who must belong to a city which is not the same as his customer and the customers should have their own grade.

Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

Sample Solution:

-- Selecting all columns from the Cartesian product of the 'salesman' and 'customer' tables
SELECT * 
-- Specifying the tables to perform the cross join ('salesman' as 'a' and 'customer' as 'b')
FROM salesman a 
CROSS JOIN customer b 
-- Filtering the results based on three conditions: 'city' in 'salesman' is not null, 'grade' in 'customer' is not null, and 'city' in 'salesman' is not equal to 'city' in 'customer'
WHERE a.city IS NOT NULL 
AND b.grade IS NOT NULL 
AND a.city <> b.city;

Output of the Query:

salesman_id	name		city	commission	customer_id	cust_name	city		grade	salesman_id
5002		Nail Knite	Paris	0.13		3002		Nick Rimando	New York	100	5001
5005		Pit Alex	London	0.11		3002		Nick Rimando	New York	100	5001
5006		Mc Lyon		Paris	0.14		3002		Nick Rimando	New York	100	5001
5007		Paul Adam	Rome	0.13		3002		Nick Rimando	New York	100	5001
5003		Lauson Hen	San Jose0.12		3002		Nick Rimando	New York	100	5001
5002		Nail Knite	Paris	0.13		3007		Brad Davis	New York	200	5001
5005		Pit Alex	London	0.11		3007		Brad Davis	New York	200	5001
5006		Mc Lyon		Paris	0.14		3007		Brad Davis	New York	200	5001
5007		Paul Adam	Rome	0.13		3007		Brad Davis	New York	200	5001
5003		Lauson Hen	San Jose0.12		3007		Brad Davis	New York	200	5001
5001		James Hoog	New York0.15		3005		Graham Zusi	California	200	5002
5002		Nail Knite	Paris	0.13		3005		Graham Zusi	California	200	5002
5005		Pit Alex	London	0.11		3005		Graham Zusi	California	200	5002
5006		Mc Lyon		Paris	0.14		3005		Graham Zusi	California	200	5002
5007		Paul Adam	Rome	0.13		3005		Graham Zusi	California	200	5002
5003		Lauson Hen	San Jose0.12		3005		Graham Zusi	California	200	5002
5001		James Hoog	New York0.15		3008		Julian Green	London		300	5002
5002		Nail Knite	Paris	0.13		3008		Julian Green	London		300	5002
5006		Mc Lyon		Paris	0.14		3008		Julian Green	London		300	5002
5007		Paul Adam	Rome	0.13		3008		Julian Green	London		300	5002
5003		Lauson Hen	San Jose0.12		3008		Julian Green	London		300	5002
5001		James Hoog	New York0.15		3004		Fabian Johnson	Paris		300	5006
5005		Pit Alex	London	0.11		3004		Fabian Johnson	Paris		300	5006
5007		Paul Adam	Rome	0.13		3004		Fabian Johnson	Paris		300	5006
5003		Lauson Hen	San Jose0.12		3004		Fabian Johnson	Paris		300	5006
5001		James Hoog	New York0.15		3009		Geoff Cameron	Berlin		100	5003
5002		Nail Knite	Paris	0.13		3009		Geoff Cameron	Berlin		100	5003
5005		Pit Alex	London	0.11		3009		Geoff Cameron	Berlin		100	5003
5006		Mc Lyon		Paris	0.14		3009		Geoff Cameron	Berlin		100	5003
5007		Paul Adam	Rome	0.13		3009		Geoff Cameron	Berlin		100	5003
5003		Lauson Hen	San Jose0.12		3009		Geoff Cameron	Berlin		100	5003
5001		James Hoog	New York0.15		3003		Jozy Altidor	Moscow		200	5007
5002		Nail Knite	Paris	0.13		3003		Jozy Altidor	Moscow		200	5007
5005		Pit Alex	London	0.11		3003		Jozy Altidor	Moscow		200	5007
5006		Mc Lyon		Paris	0.14		3003		Jozy Altidor	Moscow		200	5007
5007		Paul Adam	Rome	0.13		3003		Jozy Altidor	Moscow		200	5007
5003		Lauson Hen	San Jose0.12		3003		Jozy Altidor	Moscow		200	5007

Explanation:

This SQL query is selecting all columns from two tables, salesman and customer, and joining them using a cross join. The query also has several conditions in the WHERE clause:
The city column in the salesman table is not null
The grade column in the customer table is not null
The city column in the salesman table is not equal to the city column in the customer table.
This query is likely returning a large set of data containing all combinations of rows from the two tables, filtered by the conditions in the WHERE clause. The result set will contain all rows where the salesman and customer are from different city.

Visual Explanation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Salesmen belongs to a city, customers have a grade
Next SQL Exercise: Display each item producer company.