Hi All, Today I gonna try cloning dev.to DB at very small level. In this article, we will learn how to make relationship between tables and how to query within tables to get desired results.
At first we will have 'users' tables in which we can save our user data.
create table users (
id serial primary key not null,
first_name varchar(255) not null,
last_name text not null,
age int,
email text unique not null
)
/*
1) create table 'tableName' is a syntax for creating tables.
2) 'id' will be our unique key. Its like unique identification number of
user. It will advance in positive direction and cannot be null. To fullfill
this requirement we need below keywords together.
'serial' will move from 1 to n position.
'primary key' will sure that 'id' field will be unique always for this table.
'not null' also assures that this field cannot be null.
3) 'first_name' will be the first name of our users. It will have string to save
and it cannot be null.To fullfill we need below keywords.
'varchar(255) to save string.
'not null' to assure that this field will not be null.
NOTE : we are using 'snake_case' for first_name instead of 'camelCase'. To save
fieldname in 'camelCase' we need to give field name in double quotes like
"firstName", if we dont give name in double quotes then it became
'firstname'.
4) 'last_name' is similar to 'first_name' for saving last_name of the user.
We use 'text' for saving strings which is similar to varchar without limit.
5) 'age' will be a integer value to save users age. 'int' keyword will be use for
this.
6) 'email' will be a unique field for logging and communicating to user. It
cannot be null also.
'text' for saving email in string format.
'unique' to save unique emails only.
*/
Our 'users' table is created now but it is empty as of now, lets create some data in it.
insert into users (first_name,last_name,age,email)
values ('Earnestine','Fahey',45,'Bernita17@gmail.com');
/*
'insert into tableName(fields) values(data)' to insert data.
NOTE : We need not to specify 'id' field here because we have provided 'serial' property to it so that it will initialize and increase it automatically.
//Lets insert some more rows.
insert into users (first_name,last_name,age,email) values ('Michaela','Grant',24,'Abbie_Hintz@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Jessie','Murphy',9,'Ibrahim58@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',8,'Kayleigh_Jaskolski@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Eddie','Homenick',60,'Lea.Schuster@gmail.com');
insert into users (first_name,last_name,age,email) values ('Osbaldo','Mueller',23,'Jayden_Brekke@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Garth','Murray',50,'Mikel85@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Corene','Kilback',71,'Shyanne.Kling44@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Alexane','Fritsch',28,'Karli.Zemlak39@gmail.com');
insert into users (first_name,last_name,age,email) values ('Luz','Pouros',75,'Giovanny.Parisian@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Lavinia','Breitenberg',79,'Don55@gmail.com');
insert into users (first_name,last_name,age,email) values ('Euna','Schuppe',33,'Odessa95@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Della','Shields',38,'Brielle_Rippin58@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Loyce','Powlowski',68,'Ernest56@gmail.com');
insert into users (first_name,last_name,age,email) values ('Cecelia','Morissette',50,'Glennie_Moore39@yahoo.com');
insert into users (first_name,last_name,age,email) values ('Jody','Swift',40,'Caden56@gmail.com');
insert into users (first_name,last_name,age,email) values ('Malachi','Marks',40,'Terence.Crona88@hotmail.com');
insert into users (first_name,last_name,age,email) values ('Claud','Hegmann',29,'Madelynn_Lueilwitz65@gmail.com');
*/
In order to see our data, let hit the below query :
select * from users;
'select' means to select the fields from table.
NOTE: Here * can be replaced with fieldName such as first_name,last_name. For ease and visual reprenstation of all fields, I have used *.
If we see the values in table, some users are might less than 12 years of age, which we dont want to register. We might want to set age limit greater than 15 years. For this, we first need to delete all users who have age less than 15 years and set constraint on age to accept age only 18+.
//delete users whose age is less than 18
delete from users where age < 18;
//Set constraints on age to accept only user who are 18+
alter table users ADD CHECK (age >= 18);
//After this if we try to add user who has age less than 18 will not be allowed to register.E.g
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',8,'Kayleigh_Jaskolski@yahoo.com');
//However if user grown up and try to register then it will be able to register.
insert into users (first_name,last_name,age,email) values ('Monserrat','Stamm',18,'Kayleigh_Jaskolski@yahoo.com');
However, we can add or drop column even after table creation.
alter table users drop column age;
alter table users add column age int default 18;
WHERE keyword
With 'where' we can set condition in our select query to return only those values which satisfied the condition given.For example :
select * from users where id = 43;
select * from users where id=45 or id=2;
select * from users where id=45 and first_name = 'Eddie';
select * from users where id in (42,43,44); //Array
select * from users where age > 38;
UPDATE ROW
To update any user data, we can use update as follows:
update users set age = 20 where id = 1;
update users set age = age + 1, last_name = last_name || ' tom' where id = 1;
NOTE: To concatenate String in postgresql we can use '||'.
Now we are done with 'users' basic details, we can add if we want more but this tutorial I am advancing further with this only.We now need a 'posts' table where user's post detail can be saved in DB. Lets start with it.
Foreign Key Concept
Before moving further, we must keep in mind one thing in order to create 'posts' table that it must be linked with 'users', so that we know which user has posted that particular post. To achieve this we are going to use references which are also called foreign key concept to link both the tables.Let look how we can achieve this.
Its also called "one to many relationship"
create table posts(
id serial primary key,
title text not null,
body text default '...', //default will set '...' by default if
//no value is given.
"creatorId" int references users(id) not null
//with references we are attaching users's table primary key 'id' to 'creatorId'
//so both are tied together and our application can identify which user has created the post.
)
NOTE: we are using double quotes for showing the camelCase in postgresql.
Insert with references
insert into posts (title,body,"creatorId")
values (
'global extend supply-chains', //title
'Voluptas natus iste hic nihil ut. Ipsam delectus sed ut et blanditiis non et. Consequuntur cumque beatae quae sint voluptas nulla voluptate delectus quo. Sit libero neque est distinctio enim neque et consequuntur aut. Odio fuga voluptatem non minus.
Vero nisi non saepe occaecati illo. Delectus neque ut sint eius rerum. Expedita quo voluptas porro aut libero itaque.
Aliquid nulla similique aperiam atque assumenda ea sint cum. Est qui repellendus quia aliquid accusantium. Ea dolores perferendis libero ipsam qui. Autem itaque excepturi adipisci asperiores eaque eaque doloribus consequatur dolorum.',//body
48); // ###references ID which is userID
//Other Posts with references.
insert into posts (title,body,"creatorId") values ('efficient implement platforms','Quam aliquid odit inventore est ipsa. Nulla odio accusantium repellendus in inventore repellat laudantium ea atque. Quas aliquid et et totam. Dolor corporis sequi sequi dolorem hic. Voluptas veniam quia.
Eveniet non vitae quia sed. Alias sunt ut consequatur aut quas eius eum facilis. Molestiae quaerat optio quia aut quia nobis.
Consequatur ut iure sunt quam eum deleniti et. Minima non perspiciatis dolore nesciunt ut sit. Aliquid dignissimos tempora enim.',56);
insert into posts (title,body,"creatorId") values ('world-class reinvent web-readiness','Sit magnam vero qui. Eos quibusdam accusamus exercitationem qui et. Ratione rerum ut consequatur quibusdam id omnis quia consequatur. Quae et rem veniam beatae aut ipsum quas laudantium ex.
Sit ullam eos. Aut quae ut ipsam doloremque dolor et praesentium omnis. Et ea et beatae repudiandae neque et modi earum sed.
Impedit nesciunt dolorem maiores ut iste. Quod aliquam asperiores reprehenderit ut ad aperiam et architecto est. Eaque blanditiis quasi ut enim blanditiis qui.',53);
insert into posts (title,body,"creatorId") values ('seamless repurpose e-commerce','Est et modi sed consequatur beatae aut ad. Excepturi voluptate amet odit occaecati minus enim. Maiores non consectetur ipsam.
Delectus quod aperiam velit cum quibusdam aut totam sit impedit. Aut quis veniam enim maiores ut. Quos tempora ducimus delectus qui. Quas minima ipsum.
Necessitatibus quis labore occaecati consequatur alias autem et. Sed et nulla et vitae quidem itaque odio hic nesciunt. Ut qui itaque pariatur beatae voluptatem laborum.',56);
insert into posts (title,body,"creatorId") values ('frictionless incubate e-commerce','Deserunt facilis alias animi qui voluptatum. Amet dolor iusto fuga tempora. Et deleniti odit et. Est autem vel harum et quis laudantium. Quam quia at vel non eveniet dignissimos impedit. Dolor necessitatibus blanditiis maxime ea.
Aperiam ab aliquam ad impedit dolorem perspiciatis asperiores. Quia qui quo est eveniet eligendi maxime sequi sed reprehenderit. Officia perferendis qui animi et odit repellendus beatae. Enim qui modi omnis impedit.
Qui voluptas sequi aut assumenda. Nemo a expedita dolore. Eum quis sed ut eos. Aut nulla officia eos rerum sed optio tempore.',43);
insert into posts (title,body,"creatorId") values ('frictionless grow synergies','Esse et quos magni. Qui laborum fugiat itaque in ipsum. Ex officiis reprehenderit et quae ratione sit. Error illum nam beatae similique. Eligendi voluptatibus sint nesciunt aut iure.
Consectetur sequi ut. Possimus sit expedita omnis at. Et aut dignissimos facere non ducimus itaque. Non expedita quas sit odit. Quo aperiam eum assumenda. Consequuntur deserunt voluptate repudiandae autem officia harum totam enim minus.
Esse autem perferendis. Et illum non perspiciatis sit ex. Sed animi at optio totam nobis quas dignissimos dolorem ut.',48);
insert into posts (title,body,"creatorId") values ('leading-edge disintermediate e-commerce','Delectus et accusantium nostrum. Est et illo rerum voluptatum autem. Explicabo qui et pariatur ut. Explicabo non sit sint autem ut. Quaerat numquam rerum.
Ut odit tempora explicabo totam similique enim consequatur. Qui tempora deserunt iusto praesentium. Atque illum facilis et accusamus sint. Facilis deserunt ipsam culpa laboriosam tenetur libero. Aspernatur itaque doloribus. Assumenda qui voluptatem sequi.
Eveniet eum dolor corrupti facilis. Repellendus cum sapiente tempora illo eos. Sint omnis saepe quisquam et omnis tenetur. Voluptas quod voluptatem.',52);
insert into posts (title,body,"creatorId") values ('cutting-edge integrate methodologies','Adipisci quia veniam. Eos minus reiciendis. Cum aut nesciunt ipsa accusantium et minima aut quasi similique. Natus illo debitis saepe ut est. Sapiente nostrum sunt.
Consequuntur tenetur quidem reiciendis. Voluptatem corporis nulla asperiores ea ut quo. Sint consequatur aperiam voluptatem blanditiis earum reiciendis dolor id id. Et fugit et sed reprehenderit. Dolores mollitia pariatur molestiae et tempora ratione.
Deleniti unde sequi nesciunt. Officia itaque ratione harum sit quisquam ut illo ullam accusantium. Est illo et mollitia quo repellat.',51);
insert into posts (title,body,"creatorId") values ('seamless visualize methodologies','Est voluptatem dolorem unde omnis ipsam sunt ipsum. Ab optio esse. Quos sed sint officiis dicta sit dolorem eum. Porro itaque delectus cumque error. Voluptatem earum error nisi aut qui sunt eos.
Dolores id quia nesciunt deleniti. Consequatur sint sit provident omnis eveniet at. Sed odio suscipit minus non minima perspiciatis. Voluptate distinctio illum aliquid.
Voluptas esse omnis ea. Iusto adipisci animi id. Adipisci ut aliquam commodi qui consequatur nam minima.',45);
insert into posts (title,body,"creatorId") values ('compelling evolve synergies','Nesciunt quia quis voluptatum vel molestiae velit. Iste optio mollitia. Est a sapiente ut facere veniam quia illum molestias. Impedit fugit delectus. Voluptatem harum fugiat quis est. Minima sint qui.
Quia dicta voluptas adipisci odio beatae rem minus id. Explicabo sapiente nam quidem qui ut nisi. Autem laboriosam dicta consequatur illo sunt eos et. Tempore aut quia impedit quia neque voluptas qui sed commodi. Cumque placeat magni quia blanditiis molestiae. Occaecati aut quia perspiciatis omnis modi asperiores est possimus sit.
Sed enim consequatur non repellendus est. Laborum veniam eaque unde quae in dolore. Modi qui ut omnis exercitationem suscipit nisi. Suscipit eligendi maiores et consequatur voluptas neque sapiente. Voluptas aut aut architecto ut nisi et expedita. Cumque voluptate commodi.',47);
insert into posts (title,body,"creatorId") values ('end-to-end utilize solutions','Omnis ut vitae nihil perspiciatis sequi consequatur. Facere deleniti voluptatem et. At aut inventore porro rerum. Tempora aut est repudiandae rerum est non nam possimus iste.
Aut vel rerum repellat alias. Laudantium et reprehenderit neque vel repudiandae excepturi sed saepe rem. Quis iste reprehenderit dolores. Minus delectus nihil.
Perspiciatis porro et dignissimos soluta molestiae. Deleniti mollitia neque architecto recusandae dicta vero voluptas ullam voluptas. Asperiores magnam et enim nihil.',51);
insert into posts (title,body,"creatorId") values ('bleeding-edge matrix e-markets','Veniam et laboriosam. Quisquam odio hic iste et nihil ut quo quia quisquam. Ut et est et. Quae sequi molestias dolor modi consectetur repellendus.
Recusandae mollitia voluptate. Aut illo earum consequatur reiciendis autem est cum quos. Cupiditate vel alias. Voluptatem et omnis omnis quis vero officiis. Dolorum hic a libero sunt praesentium vero inventore non.
Doloremque at ut commodi. Illum deserunt alias rerum perspiciatis rerum deserunt dicta iste necessitatibus. Dolor porro qui ut ea.',46);
insert into posts (title,body,"creatorId") values ('intuitive orchestrate web services','Laboriosam vitae amet aut vitae quibusdam eos. Voluptatem temporibus totam aperiam consequatur. Sit non alias voluptate reprehenderit dolorum. Esse ut qui quisquam sed eum eum illum. At consectetur dolor nam cupiditate magnam et laboriosam.
Adipisci in consequatur sapiente ea ut laudantium unde. Accusamus explicabo ut. Qui occaecati sit. Numquam excepturi et aspernatur. Ipsum cupiditate optio id fugiat nostrum ea voluptatem numquam totam.
Voluptate ut praesentium. Quis eaque sequi laboriosam ad optio. Qui magni quia voluptatibus eaque excepturi. Voluptatem quo voluptas voluptatem repudiandae. A sunt illo sint dolores rem omnis.',52);
insert into posts (title,body,"creatorId") values ('B2C reinvent methodologies','Numquam aut sint pariatur quam quo dignissimos quia in. Tempore officiis suscipit ut ullam ipsa id. Minima reprehenderit culpa maxime cum quidem quia qui. Pariatur in molestias deleniti omnis provident id reprehenderit aspernatur. At exercitationem similique veniam id ut.
Maiores est odio quia dolorum voluptatem doloremque et. Omnis omnis rerum nesciunt ad quaerat cum quasi. Et voluptatum deserunt cumque consequatur. Deleniti cumque alias qui ratione.
Facere a nihil placeat ea deleniti aut ut repellat. Est ut deleniti est consequatur. Incidunt similique dicta quis suscipit aliquid pariatur voluptatem cumque.',53);
insert into posts (title,body,"creatorId") values ('killer morph initiatives','Nostrum aliquam molestias iusto est ab nostrum et. Et ratione nobis sunt. Qui autem sit porro quo. Commodi in molestiae modi vel qui accusantium. Quibusdam ullam ut nulla quia autem. Eos ut temporibus nobis aspernatur et.
Necessitatibus vitae odio necessitatibus ut. Aut amet qui explicabo assumenda quisquam ex omnis. Quis dolor debitis eos enim totam vitae totam consequuntur itaque.
Fugit at id odio fuga officiis reprehenderit odit rerum. Neque iure aut et at sit dolorum laborum asperiores debitis. Omnis quidem quis. Culpa libero animi.',45);
insert into posts (title,body,"creatorId") values ('leading-edge whiteboard e-commerce','Ab ratione officiis quis sunt et. Enim id vel iste temporibus. Deleniti est veritatis quia magni consequatur. Sint inventore odio nesciunt explicabo. Est dignissimos ducimus deleniti sit.
In in ut qui. Ut in placeat blanditiis tempora cum minus veritatis. Quasi architecto eligendi atque debitis ut autem accusantium excepturi sed.
Vel possimus aut totam est quo consequatur sed consequatur. Cum voluptas dolores distinctio aut aut aut laborum et eaque. Voluptatem reprehenderit ut omnis voluptas eius in magni blanditiis. Ut totam non dolorem est similique aperiam magni tempora quaerat.',50);
insert into posts (title,body,"creatorId") values ('collaborative repurpose initiatives','Asperiores sapiente quos eaque error id aut perferendis fuga dicta. Voluptatem magnam in suscipit distinctio esse officia a nemo. Vitae vero voluptate.
Quia veniam placeat delectus et magnam placeat nam libero blanditiis. Blanditiis et sapiente saepe animi. Enim enim dolorem nobis adipisci eos omnis fuga a blanditiis. Ratione animi voluptas earum in provident asperiores nihil nemo omnis. Asperiores debitis minima eveniet corrupti.
Expedita omnis expedita laboriosam vel. Distinctio harum mollitia deserunt tempora explicabo distinctio repellendus ut numquam. Qui provident aperiam tenetur recusandae.',43);
insert into posts (title,body,"creatorId") values ('compelling deliver technologies','Praesentium quisquam rerum dolore corporis hic dolor nihil delectus. Possimus quam modi iusto sit deleniti praesentium. Enim sint deleniti vel cupiditate cum aspernatur. Aliquam occaecati ullam est expedita odio pariatur corrupti molestiae accusamus.
Ut ad numquam sed beatae assumenda. Enim animi voluptas non magni. Voluptatem consequatur rerum ea.
Dolores vero et eius aut iusto quasi. Modi sit molestiae adipisci ex ut aut animi libero. Autem at culpa velit debitis sit cumque.',47);
insert into posts (title,body,"creatorId") values ('revolutionary redefine e-markets','Repellendus commodi officiis qui fugiat aut qui perspiciatis omnis vero. Fugiat totam eaque. Voluptatem saepe laboriosam quo atque rerum eaque dolores. Quidem doloremque repellendus et nostrum dolore possimus ut adipisci. Porro corrupti quae et sit. Alias ipsum est aut molestias cum perspiciatis aspernatur doloribus.
Impedit et beatae placeat consequuntur aut id aperiam. Sed molestiae consectetur necessitatibus iure qui itaque hic. Modi et et quia neque nisi aut error.
Et modi maiores reprehenderit doloribus accusamus culpa ab saepe voluptatem. Quod quas quia eaque qui eveniet facere quidem. Non nihil laboriosam et adipisci omnis minus voluptate sit facere. Occaecati sunt velit doloremque facere et ea repudiandae. Dolores soluta ex deleniti non. Exercitationem id at qui quae non aut molestiae autem saepe.',43);
insert into posts (title,body,"creatorId") values ('web-enabled recontextualize partnerships','Iste id est. Est sed expedita nam explicabo nam et enim. Nam labore exercitationem in repellat aut. Corporis ratione aspernatur sit voluptatibus molestiae sit aut.
Aut nihil nulla accusamus quam praesentium voluptas minus voluptatem. Voluptate fugit aut debitis ducimus deserunt doloribus recusandae fugit quaerat. Voluptates tempora tenetur minima voluptatem non. Occaecati aut id vero consequatur maiores excepturi.
Veritatis ratione iste dolorem quisquam aliquam quod nostrum exercitationem. Cumque sapiente id ut eum saepe. Magnam expedita porro exercitationem ut consequuntur.',57);
insert into posts (title,body,"creatorId") values ('leading-edge matrix bandwidth','Voluptas ut quam ut dolorum. Voluptatem nam consectetur facere dicta nostrum minus. Saepe facere laborum veritatis voluptate iste aut.
Fuga qui aut laboriosam atque dolore qui ipsum repudiandae et. Quos deleniti occaecati alias nobis voluptatem libero enim. Vel non ea. Earum ut sequi.
Esse repellat eum sit. Saepe rerum tempora voluptatibus quia ipsum. Quam accusantium id unde aliquam. Explicabo dolores minima et modi consequuntur fugit ducimus. Maiores numquam magnam.',49);
insert into posts (title,body,"creatorId") values ('collaborative harness infomediaries','Illum fugiat dicta quae cupiditate eum voluptas cum eos optio. Voluptas aut voluptatem iste. Sunt non sint illum.
Quos fugit fugiat et beatae molestiae expedita eveniet sit. Asperiores officia nihil aut. Eum et dolor quasi. Repellendus doloremque aut officia error quis amet molestiae. Maxime perferendis quibusdam unde magnam. Ea voluptas unde qui velit magnam aliquam.
Quia voluptas est sed veritatis neque modi. Voluptas cum consectetur recusandae ullam. Sed culpa provident eos quae et animi sit quo harum. Cum enim eligendi.',50);
insert into posts (title,body,"creatorId") values ('24/365 strategize markets','Dolor cumque adipisci sequi ipsum sunt. Quo voluptate qui deserunt placeat facere aut ullam. Temporibus enim facere voluptas cum doloremque eos ut. Odit quia ea laborum dicta voluptatem eum sed et aspernatur.
Possimus reiciendis saepe fugit nihil est quos voluptatibus unde quis. Enim saepe amet. Ut quos doloribus dolorem vitae dolor corporis reiciendis. Architecto accusantium velit eos perferendis optio. Totam sunt placeat quos reprehenderit. Sit quia rem exercitationem ex praesentium autem eligendi animi quasi.
Non praesentium fugit et perferendis aspernatur nisi nesciunt. Magni fuga rerum. Nisi eum blanditiis illo temporibus sit quae. Earum in repudiandae eius voluptatem ullam qui.',55);
insert into posts (title,body,"creatorId") values ('open-source embrace networks','Consectetur in recusandae natus aut unde ad provident harum repudiandae. Omnis nam aut autem quia voluptas repudiandae asperiores voluptate atque. Totam sapiente accusamus eum velit tempora porro velit.
Voluptatibus aut fuga incidunt et cumque. Ut reprehenderit repudiandae quia. Reiciendis dolor explicabo quis incidunt consequatur nostrum quam. Hic quos sed. Eum incidunt sunt distinctio aspernatur quia est.
Et omnis eos accusantium omnis architecto a. Consequatur commodi consequuntur. Id in doloremque quisquam at iusto. Natus autem sed temporibus voluptatem sit. Explicabo impedit molestiae nulla velit veniam accusantium.',52);
insert into posts (title,body,"creatorId") values ('back-end target action-items','Quo voluptatem libero adipisci et dolor laboriosam in. Magni ex facilis. Unde laudantium laborum aut sequi aut eos et commodi. Illo sed aut dolorem. Voluptates nihil consequatur.
Officia quisquam commodi eaque laborum doloremque magnam cupiditate. Et maiores cumque rerum magnam beatae. Reiciendis voluptatem et. Totam modi quae velit quo blanditiis facilis veritatis quibusdam. Harum est aut maiores eos distinctio necessitatibus quia non. A necessitatibus facere at culpa ducimus maxime maiores.
Recusandae aut suscipit est. Sint et quae. Et animi amet et laboriosam iste eum est. Quia omnis laudantium eum voluptatem labore quos impedit commodi vel.',52);
insert into posts (title,body,"creatorId") values ('B2C envisioneer users','Ullam et quae deserunt repellat earum. Delectus voluptates consequatur corrupti dolorem culpa. Ducimus velit impedit voluptatem.
Officiis odio voluptate eaque ad laboriosam esse aut. Est atque mollitia veritatis. Tempore exercitationem distinctio repudiandae voluptatum illo nihil possimus sequi aliquid.
Qui rerum hic. Nulla vel architecto quaerat minima non et qui suscipit. Sed eaque ut.',44);
Now as we have successfully created posts for some of our user. Now we want to see data which combines both posts and users data together. For this we will be using 'join'.
Join can be of many types:
1) Inner Join
2) Left Join
3) Right Join
4) Outer Join
5) cross join
6) natural join
Inner Join
With the help of Inner Join keyword we can get the from both the table on which join has been called. For example
select * from users u inner join posts p on u.id = p."creatorId";
//Inner Join defines the type of Join
//ON decides the condition.
//This command join left table with right table.
//In this query, we are matching users table id with posts table "creatorId" as //we have a foreign key relationship in between them.
// Here we takes users as 'u' , posts as 'p'.
select u.id, p.id,first_name,title from users u left join posts p on u.id = p."creatorId";
select u.id, p.id,first_name,title from users u inner join posts p on u.id = p."creatorId";
//Difference between left and inner join is, left join will show you values of //left table even condition not satisfy.
Check Post of particular user with all details.
select u.id, p.id post_id,first_name,title from users u inner join posts p on u.id = p."creatorId" where u.id = 45;
Check post from particular user with particular title.
select u.id, p.id post_id,first_name,title from users u inner join posts p on u.id = p."creatorId" where p.title like '%killer%';
NOTE : --- word% to find after the word.
--- %word% to find in between.
--- %word find before the word
--- To get Case insensitive we can use 'ilike'
Before moving further lets create our new table 'comments' who can handle comments of post.
create table comments(
id serial primary key,
message text not null,
post_id int references posts(id) not null,
creator_id int references users(id) not null
)
//Insert comments
insert into comments (message,post_id,creator_id) values ('Odit cupiditate aliquid aut quaerat tempora ut.',93,43);
insert into comments (message,post_id,creator_id) values ('Hic et et quas doloribus id repellat aut asperiores.',100,49);
insert into comments (message,post_id,creator_id) values ('Officiis magnam dolorum qui nihil et qui rerum consequatur accusantium.',94,50);
insert into comments (message,post_id,creator_id) values ('Adipisci blanditiis quis.',103,48);
insert into comments (message,post_id,creator_id) values ('Repellendus id natus.',94,55);
insert into comments (message,post_id,creator_id) values ('Quas corrupti mollitia dolorem explicabo.',97,50);
insert into comments (message,post_id,creator_id) values ('Commodi iusto est et.',103,51);
insert into comments (message,post_id,creator_id) values ('Vero maxime inventore delectus voluptatum sapiente doloremque distinctio alias.',106,52);
insert into comments (message,post_id,creator_id) values ('Ipsum maiores maiores sit distinctio nobis velit ut et.',106,46);
insert into comments (message,post_id,creator_id) values ('Quo illo sit quasi temporibus.',104,52);
insert into comments (message,post_id,creator_id) values ('Animi aperiam sed voluptas.',108,47);
insert into comments (message,post_id,creator_id) values ('Et quaerat quod quisquam quaerat consectetur fugiat accusantium.',95,44);
insert into comments (message,post_id,creator_id) values ('Amet eum sit consequatur accusantium sapiente temporibus a.',99,55);
insert into comments (message,post_id,creator_id) values ('Et aut fuga quod.',105,52);
insert into comments (message,post_id,creator_id) values ('Eveniet temporibus dolorem in cumque placeat et vitae.',95,53);
insert into comments (message,post_id,creator_id) values ('Explicabo velit quae sapiente adipisci omnis et consequatur accusantium.',98,45);
insert into comments (message,post_id,creator_id) values ('Ab dolorem cum aspernatur quia.',102,47);
insert into comments (message,post_id,creator_id) values ('Saepe debitis sint sunt.',101,53);
insert into comments (message,post_id,creator_id) values ('Exercitationem non voluptatem sed nostrum libero.',99,57);
Now lets make a multiple inner joins call upon comments,posts,users table.
//Basic Call
select * from comments;
//Lets find post title where comment has been given.
select c.id, c.message,c.creator_id commentor_id,p.title,p.id post_id from comments c
inner join posts p on c.post_id = p.id;
//Lets find post creator Id on which comment has been given.
select c.message,
p.title,p.id,
u.id user_id_for_post,u2.id user_id_for_comment
from comments c
inner join posts p on c.post_id = p.id
inner join users u on p."creatorId" = u.id
inner join users u2 on c.creator_id = u2.id;
Till now we have done 1 to many relationships, Now we are going to do Many to Many relationships.
create table favorites (
user_id int references users(id),
post_id int references posts(id),
primary key (user_id,post_id) --- Composite keys
)
//favorites is same as like or loved anything you can say.
// It is many to many relationship.
//Because a single user can favorited multiple post and vice-versa.
create table friends (
user_id1 int references users(id),
user_id2 int references users(id),
primary key (user_id1,user_id2) //composite keys
)
//Same for friends table.
//One user can be friends with other user.
First of all lets delete entire data of our tables and create new data for aligning all tables.
TRUNCATE TABLE users,posts,comments,favorites,friends RESTART IDENTITY;
//Truncate can be used to delete tables data.
//RESTART IDENTITY is used to start primary key from start.
Use Data and paste it in your postgresql.
Count number of comments on particular post
select count(*) from comments c
inner join users u on c.creator_id = u.id
where c.post_id = 7;
Order By,Limit, Offset
Order By
Whenever we want our results from query to follow a particular order based on any field, we can use 'order by' for this.
select * from users order by age;
//Above query will give us results based on age in increasing order, If we want //to change the order we can use **asc or desc** at the end to change order.
select * from users order by age asc;
select * from users order by age desc;
Limit
Whenever we want to limit the result of our query we can use 'limit' for this.
select * from users order by age limit 10;
offset
If we want to set pagination in our app, then we can use 'limit' and 'offset' together for showing this functionality like below example.
select * from users order by age offset 20 limit 10;
//offset will start reading query from that particular point in our query, it
//will start reading from '20' upto limit '10'
Find a details about post with its post_title, post_writer,comments_writer , comments_message and user_id who favorites it.
select p.title,u.first_name post_writer,c.message,u2.first_name comment_writer,
f.user_id from posts p
inner join users u on p."creatorId" = u.id
inner join comments c on p.id = c.post_id
inner join users u2 on c.creator_id = u2.id
left join favorites f on f.post_id = p.id
where p.id = 7;
Top comments (2)
It's a great article. Would love if you could add an index, for easy traversal.
Sure will do that.