SQL JOIN Queries – How to use SQL JOINS and What do SQL JOINS mean?

SQL JOIN Queries – How to use SQL JOINS and What do SQL JOINS mean?

The work JOIN basically means to link or connect two entities. In SQL this is used to connect / join two tables.

What are the different types of JOINS and how are they different?

TYPE OF JOIN	DESCRIPTION
INNER JOIN	Returns all rows when there is at least one match in BOTH tables
LEFT JOIN	Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN	Return all rows from the right table, and the matched rows from the left table
FULL JOIN	Return all rows when there is a match in ONE of the tables

 

Example:

Note:

There is an Educational_Purpose_DB on USFLDFBSQL004 where the following queries can be executed

Query:

/* SELECT ALL ROWS FROM STORE_TABLE_A */

SELECT * FROM Store_Table_A

Result:

ID	STORE_FIRST_NAME
1	WAL
2	PUB
3	MA
4	JC
7	BASKIN

 

Query:

/* SELECT ALL ROWS FROM STORE_TABLE_B */

SELECT * FROM Store_Table_B

Result:

ID	STORE_LAST_NAME
1	MART
2	LIX
3	CY
4	PENNY
5	DONALDS
6	DONUTS

 

Query:

/* INNER JOIN */

--SELECT ALL THAT ARE IN COMMON BETWEEN THE TWO TABLES

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN INNER JOIN WITH TABLE B

FROM Store_Table_A AS A INNER JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY

 

Query:

/* LEFT JOIN */

--SELECT ALL THAT ARE IN LEFT TABLE AND WHATEVER IS COMMON BETWEEN THE TWO TABLES

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN LEFT JOIN WITH TABLE B

FROM Store_Table_A AS A LEFT JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY
7	NULL	BASKIN	NULL

 

Query:

/* RIGHT JOIN */

--SELECT ALL THAT ARE IN RIGHT TABLE AND WHATEVER IS COMMON BETWEEN THE TWO TABLES

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN RIGHT JOIN WITH TABLE B

FROM Store_Table_A AS A RIGHT JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY
NULL	5	NULL	DONALDS
NULL	6	NULL	DONUTS

 

Query:

/* FULL JOIN */

--SELECT ALL THAT ARE IN BOTH TABLES AND WHATEVER IS NOT COMMON BETWEEN THE TWO TABLES AS WELL

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN RIGHT JOIN WITH TABLE B

FROM Store_Table_A AS A FULL JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY
NULL	5	NULL	DONALDS
NULL	6	NULL	DONUTS
7	NULL	BASKIN	NULL

 

*If the primary key do not exist in the second table [for left, right and full joins], the values will be displayed as NULL

How to create a user / add a user in SQL Management Studio / SQL Server

1. Remote onto the server

2. Open SQL Server Management Studio

 

 

 

3. Log onto the SQL Server using windows pass through authentication

 

 

 

4. Once logged onto the SQL Server, scroll to Security > Logins

 

 

 

5. Right click on Logins node and click on New Login

 

 

 

6. FOR ADDING USER WITH WINDOWS AUTHENTICATION FOLLOW THESE STEPS, FOR SQL AUTHENTICATION FOLLOW STEP 7

• Select General in the Select a Page section and make sure you chose Windows authentication and click on search

• Once you click on Search, you will be provided with the following window, choose the appropriate location.

• Add the appropriate user and click ok

• AFTER THIS STEP PLEASE FOLLOW STEP 8 AND ONWARDS

7. FOR ADDING USER WITH SQL AUTHENTICATION FOLLOW THESE STEPS

• Select General in the Select a Page section and make sure you chose SQL Server authentication

• Put in the desired login name and password in password as well as the confirm password section; Passwords should be at least 8 characters.

• Check Enforce policy; Uncheck Enforce password policy only if you want to turn off account lockout feature on multiple incorrect password attempts

8. Choose the Default database; if known at creation of account or leave the setting to be master

 

 

 

9. Choose User Mappings in the Select a page section and in the Users mapped to this login section, choose the appropriate database. Also in the Database role membership for: , choose the appropriate membership roles.

 

 

 

MORE INFORMATION ON ROLE MEMBERSHIP:

Database-level role name	Description
db_owner	Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin	Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin	Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator	Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin	Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter	Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader	Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

IF YOU DO NOT KNOW WHAT ROLE TO CHOOSE, IT IS BEST TO CHOOSE DB_DATAREADER FOR USERS WHO WILL ONY READ DATA; ALSO IT IS BEST TO CHOOSE DB_DATAWRITER FOR USERS WHO WILL ONLY ADD, MODIFY AND DELETE DATA BUT NOT READ.

MOST USER ACCOUNTS ARE CREATED ARE USED FOR BOTH READING AND UPDATING DATA HENCE IN THIS CASE THE USER WILL HAVE TO HAVE DB_DATAREADER AND DB_DATAWRITER.

10. Choose Status in Select a page section; Make sure the permission to connect to database engine is Grant and Login is Enabled

 

 

 

11.Click OK; you are all set!

IN CASE YOU ENCOUNTER THIS ISSUE WHILE CREATING AN USER:

The issue is with the password and these are the rules of the password in SQL server:

The password must meet the Microsoft SQL Server Strong Password requirements:

Does not contain all or part of the user's account name

Is more than eight characters in length

Contains characters from at least three of the following categories:

English uppercase characters (A through Z)

English lowercase characters (a through z)

Base 10 digits (0 through 9)

Nonalphabetic characters (for example: !, $, #, %)