MySQL CROSS JOIN

 How to Work Cross Join:

Example:

Cross Join Syntax:

Code:
CREATE TABLE city(

cid INT NOT NULL AUTO_INCREMENT,

cityname VARCHAR(50) NOT NULL,

PRIMARY KEY (cid)

);

insert records in "city" table

INSERT INTO city(cityname)

VALUES('Mul'),

('Lhr'),

('Mul'),

('Bhw'),

('Mul');

create table "personal" and insert records

CREATE TABLE personal(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

percentage INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

city INT NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid)

);

INSERT INTO personal(id,name,percentage,age,gender,city)

VALUES

(1,"Aqib","45","13","M",1),

(2,"Sheikh Anum","56","21","F",2),

(3,"Salman Khan","62","20","M",1),

(4,"Aqeela","47","18","F",3),

(5,"Ali","74","22","M",1),

(6,"Hamza","64","21","M",2),

(7,"Shahid","52","20","M",1);

CROSS JOIN

SELECT p.id,p.name,c.cityname 
FROM personal p CROSS JOIN city c;

SELECT p.id,p.name AS Name,c.cityname AS City 
FROM personal p CROSS JOIN city c;

SELECT p.id,p.name AS Name,c.cityname AS City 
FROM personal p , city c;  // ( , ) same work cross join