Assuming you're joining on columns with no duplicates, which is a very common case:
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
Examples
Suppose you have two tables, with a single column each, and data as follows:
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Right outer join
A right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a(+) = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
You will need to adjust the permissions after that.
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
OR
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
Notes:
- There is no space between the option
-p
and the password. If your database has no password, remove the -u username -ppassword
part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema
error). If that is the case, use a traditional way to clone a database and then drop the old one:
mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
Best Answer
GRANT
s on different objects are separate.GRANT
ing on a database doesn'tGRANT
rights to the schema within. Similiarly,GRANT
ing on a schema doesn't grant rights on the tables within.If you have rights to
SELECT
from a table, but not the right to see it in the schema that contains it then you can't access the table.The rights tests are done in order:
Your confusion may arise from the fact that the
public
schema has a defaultGRANT
of all rights to the rolepublic
, which every user/group is a member of. So everyone already has usage on that schema.The phrase:
Is saying that you must have
USAGE
on a schema to use objects within it, but havingUSAGE
on a schema is not by itself sufficient to use the objects within the schema, you must also have rights on the objects themselves.It's like a directory tree. If you create a directory
somedir
with filesomefile
within it then set it so that only your own user can access the directory or the file (moderwx------
on the dir, moderw-------
on the file) then nobody else can list the directory to see that the file exists.If you were to grant world-read rights on the file (mode
rw-r--r--
) but not change the directory permissions it'd make no difference. Nobody could see the file in order to read it, because they don't have the rights to list the directory.If you instead set
rwx-r-xr-x
on the directory, setting it so people can list and traverse the directory but not changing the file permissions, people could list the file but could not read it because they'd have no access to the file.You need to set both permissions for people to actually be able to view the file.
Same thing in Pg. You need both schema
USAGE
rights and object rights to perform an action on an object, likeSELECT
from a table.(The analogy falls down a bit in that PostgreSQL doesn't have row-level security yet, so the user can still "see" that the table exists in the schema by
SELECT
ing frompg_class
directly. They can't interact with it in any way, though, so it's just the "list" part that isn't quite the same.)